Feeds:
Posts
Comments

Archive for June, 2018

I have a dataset in my tabular model that looks like below:

As you see, when you sum it for the IssueReportId of 9825, it will return with the total duration of 51, instead of 17.  The end result should look like below:

In SQL, this can be done using the following query where you get the max duration for each IssueReportId first and then add them together.

SELECT IssueReportId

      ,BodyLocationOfInjury

      ,MAX(Duration) AS Duration

FROM IssueReports

GROUP BY IssueReportId

        ,BodyLocationOfInjury

 

To do the above SQL in DAX, use the SUMMARIZE function and then use the SUMX function to sum them up in the tabular model.

Sum of Duration:=SUMX(SUMMARIZE(‘Issue Reports’, ‘Issue Reports’[IssueReportId], “Sum of Duration1”, CALCULATE(MAX(‘Issue Reports’[Duration]))), [Sum of Duration1])

 

Syntax:

SUMMARIZE(<table>, <groupBy_columnName>[, <groupBy_columnName>]…[, <name>, <expression>]…)

https://msdn.microsoft.com/en-us/query-bi/dax/summarize-function-dax

SUMX(<table>, <expression>)

Advertisements

Read Full Post »

%d bloggers like this: