topic Re: Dax Outer Join in DAX Commands and Tips

Dax Outer Join

ryand009 — Fri, 09 Dec 2022 17:01:02 GMT

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

Re: Dax Outer Join

Greg_Deckler — Fri, 09 Dec 2022 19:00:55 GMT

ryand009 Maybe:

EVALUATE ROW ( "b", CALCULATE ( COUNTROWS ( ExecutionLogStorage ), 'Catalog'[CreationDate] >= DATE ( YEAR ( NOW () ), MONTH ( NOW () ) - 6, DAY ( NOW () ) ) ) + 0 )

Re: Dax Outer Join

Anonymous — Mon, 12 Dec 2022 07:53:00 GMT

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

Re: Dax Outer Join

ryand009 — Mon, 12 Dec 2022 11:06:37 GMT

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