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.