Feeds:
Posts
Comments

Archive for the ‘Uncategorized’ Category

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 »

Apparently Microsoft has redesigned the UI of the SSRS Report Manager in particular the Data Sources bit.  In SSRS 2016, you have a dropdown list to select the credential type i.e. Windows user name and password or Database user name and  password. In the previous version, you need to tick a box that says “Use as Windows credentials when connecting to the data source”.

When I first set up my data source on SSRS 2016 report manager, I ticked the box that says “Log in using these credentials, but then try to impersonate the user viewing the report”. It was a habit that as soon as I put my credential in,  I would tick the box.

Previous version:

SSRS 2016 version:

 

Read Full Post »

%d bloggers like this: