pietlinden (4/14/2016)
happycat,I was wondering how to do it in DAX without "cheating" (well, if you read Rob Collie's book, he does everything explicitly).
The basic idea of what I wanted was basically this:
in step 1 (inner query), get MAX(Grade) Per Symptom Per Patient.
in step 2 (outer query), get Counts of PatientID for each (Symptom, MaxGrade).
I was looking at Evaluate and SUMMARIZE, but that just completely boggled my mind.
thanks!
Pieter
First - if you want to see what clicking on the tool bar SUM icon does, just look at it does. The result will be that a formula is created somewhere in the calculation area (probably towards the left). If you were to type this same value into the cell (something like "MAX of columnname:=MAX(ColumnName)", it would have exactly the same meaning. You may want to change the name to something that means something to the business of course.
I think that the way you are expressing the step 1 and step 2 requirement is not quite how to think when using SSAS/DAX. You do not normally need to deal with the way the query processor handles the query. Focus more on the result "Get the highest (MAX) grade and the number of patients grouped by patient and by symptom. Unless there are any particular business rules around this calculation, the model needs the MAX calculation and either a COUNT or DISTINCTCOUNT of patients. The rest (the grouping) are simply report requirements..drag patient and symptom onto the report along with the 2 calculations and SSAS/DAX know that the grouping is needed. THat is how the language works.
Of course, you do need to have the correct relationships designed into your model first.