Retaining unique row combinations from query preview to discover
In my SQL query preview section (first screen grab) which has two columns, 'Section_Reference' and 'Max_NSP_24Months', I have unique row combinations.
I'd essentially like what is seen in the preview section to be replicated as a discover so that each Section_Reference has it's corresponding Max_NSP_24Months value.
However, in the data model section (second screen grab), which ever measure function is used (whether that be Sum or Average), that function considers the entire Max_NSP_24Months column and applies the result to each Section_Reference (see third screen grab showing the sum of the entire Max_NSP_24Months column being applied to each Section_Reference).
Any help/advice would be greatly appreciated.
It looks to me, without seeing the whole model, that the table containing Section_ Reference and Max_NSP_24Months is not properly joined to the Table containing the Section_Reference column used in the Discover Query. As there is no valid join, you are getting all Max_NSP_24Months values aggregated for each Section_Reference.
Please check the above and / or provide further information here on the rest of the model so we may help you further.
Hope that helps.
Have you resolved this or do you still need some help? As above, we'd need some detail on your model to help further.