April 13, 2016 at 3:04 pm
This is more of an "Is this possible in PowerPivot" than an emergency or whatever...
I have a table in my database that describes symptoms that a patient experiences, and they're graded by severity (1 to 5). When I do the reporting, they only want to know about the highest grade symptom per (patient, symptom). In T-SQL, this is stupid easy... it's just a totals query.
SELECT PatientID
, SymptomName
, MAX(Grade)
FROM Symptoms
GROUP BY PatientID
SymptomName;
My question is this... assuming I can't write my own queries (because I don't have rights on the SQL Server), how do I write something like this in DAX? (I was looking at ADDCOLUMNS etc, but that mess just boggled my mind!!!)
If it's in one of the books on DAX, which one? I have all of them except Matt Allington's book.
Here's the basic structure of the Symptoms table...
CREATE TABLE PatientSymptoms (
EnrolleeID INT
,SymptomID INT,
,Grade TINYINT NOT NULL
,Cycle TINYINT -- surrogate for DATE
CONSTRAINT pkPatientSymptoms PRIMARY KEY (EnrolleeID, SymptomID, Cycle) )
The Symptom names are in another table...
--- I'm just wondering how to get my head around doing the summaries (totals queries) in DAX instead of T-SQL... I know I can use the SQL window in PowerBI and/or Excel, but I was wondering if there were another way.
Thanks!
Pieter
April 13, 2016 at 10:10 pm
Sure this is simply the MAX aggregration. Click on the column name and select "MAX" from the aggregation tool bar item. This default to SUM...change SUM to MAX and away you go
April 14, 2016 at 5:05 pm
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
April 14, 2016 at 9:53 pm
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.
April 15, 2016 at 8:50 am
I'm okay at SQL, so I sort think of DAX in terms of SQL, which is clearly not the best way to do it. Might have to re-read all those chapters on how DAX calculates values. (Like in Rob Collie's book?)
April 19, 2016 at 9:09 pm
If you can step back from SQL and really consider what the user requires - e.g. I want to know the values of sales (which may include by Region, By Month etc).
With SSAS, you normally do not need to worry too much about the equivalent of SQL "GROUP BY". This tends to be a report requirement that is satisfied by the report designer. Remember that the report design may never be formally specified especially if end user authoring (e.g. Power View, Power BI) is being used. SSAS is really good at deally with that.
You need to make sure that the measures are available such as SUM (SalesAmount) and correctly reflect any business rules - you may have conditions etc to use from time to time as part of those rules.
Assuming that all tables in your SSAS database are correctly related to each other, most reporting requirements can be "met" with simple drag and drop. SSAS handles the navigation (equivalent of JOINs) because your SSAS database has them in it and it uses them automatically (unlike SQL relational databases with force you to repeat the JOIN logic every time.
April 20, 2016 at 12:10 am
I'm just slow...
This worked:
HighestGrades = SUMMARIZE('Toxicity'
,Toxicity[Protocol]
,Toxicity[PatientKey]
,Toxicity[Symptom]
,"Worst Grade",MAX(Toxicity[Grade]))
Then I based my reporting on that.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply