Dax Outer Join

[ryand009]

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

[v-rzhou-msft]

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

[ryand009]

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

[Greg_Deckler]

@ryand009 Maybe: 

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