SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
pietlinden
pietlinden
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53387 Visits: 16991
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
happycat59
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26671 Visits: 3295
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
pietlinden
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53387 Visits: 16991
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
happycat59
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26671 Visits: 3295
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
pietlinden
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53387 Visits: 16991
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
happycat59
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26671 Visits: 3295
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
pietlinden
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53387 Visits: 16991
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search