create summaries in DAX in Excel/PowerBI (not Analysis Services)

  • pietlinden

    SSC Guru

    Points: 62365

    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

  • happycat59

    One Orange Chip

    Points: 29186

    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

  • pietlinden

    SSC Guru

    Points: 62365

    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

  • happycat59

    One Orange Chip

    Points: 29186

    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.

  • pietlinden

    SSC Guru

    Points: 62365

    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?)

  • happycat59

    One Orange Chip

    Points: 29186

    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.

  • pietlinden

    SSC Guru

    Points: 62365

    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 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply