Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello
I’m building a PBI report to look at SSRS report usage. I need a measure that returns the usage of reports created in the last six months. Two tables used in the measure
Catalog table --> Contains report details, attributes like ReportID, name, createdDate etc.
ExecutionLogStorage table --> Contains report usage details, attributes like ReportID, UserId, TimeStart etc.
Measure currently is …
EVALUATE
ROW (
"b",
CALCULATE (
COUNTROWS ( ExecutionLogStorage ),
'Catalog'[CreationDate]
>= DATE ( YEAR ( NOW () ), MONTH ( NOW () ) - 6, DAY ( NOW () ) )
)
)
This only returns reports that have had usage. I need it to return all reports in the last 6 months, even if the usage is 0, I guess I need the DAX equivalent of a left outer join.
Any suggestion much appreciated.
Thaks in advance.
Roy
Hi @ryand009 ,
Could you tell me if your issue has been solved? If it is, kindly Accept the helpful reply as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your issue or share me with your pbix file without sensitive data.
Best Regards,
Rico Zhou
Greg
Thanks for the quick reply.
I added the +0 to the measure, but it returns all reports and seems to be overwriting the filter ???
@ryand009 Maybe:
EVALUATE
ROW (
"b",
CALCULATE (
COUNTROWS ( ExecutionLogStorage ),
'Catalog'[CreationDate]
>= DATE ( YEAR ( NOW () ), MONTH ( NOW () ) - 6, DAY ( NOW () ) )
) + 0
)
User | Count |
---|---|
50 | |
23 | |
18 | |
18 | |
14 |
User | Count |
---|---|
91 | |
84 | |
43 | |
26 | |
21 |