Dax Outer Join

Question

Hello&nbsp;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&nbsp;Catalog table&nbsp;--&gt;&nbsp;Contains report details, attributes like&nbsp; ReportID, name, createdDate etc.ExecutionLogStorage table&nbsp;--&gt;&nbsp;Contains report usage details, attributes like ReportID, UserId, TimeStart etc.&nbsp;&nbsp;Measure currently &nbsp;is …EVALUATEROW&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;"b",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;COUNTROWS&nbsp;(&nbsp;ExecutionLogStorage&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Catalog'[CreationDate]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&gt;=&nbsp;DATE&nbsp;(&nbsp;YEAR&nbsp;(&nbsp;NOW&nbsp;()&nbsp;),&nbsp;MONTH&nbsp;(&nbsp;NOW&nbsp;()&nbsp;)&nbsp;-&nbsp;6,&nbsp;DAY&nbsp;(&nbsp;NOW&nbsp;()&nbsp;)&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;))&nbsp;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.&nbsp;Any suggestion much appreciated.Thaks in advance.Roy&nbsp;

greg_deckler · Answer

ryand009&nbsp;Maybe:&nbsp;
EVALUATE
ROW (
    "b",
        CALCULATE (
            COUNTROWS ( ExecutionLogStorage ),
            'Catalog'[CreationDate]
                &gt;= DATE ( YEAR ( NOW () ), MONTH ( NOW () ) - 6, DAY ( NOW () ) )
        ) + 0
)

anonymous · Answer

Hi&nbsp;ryand009&nbsp;,
&nbsp;
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 · Answer

Greg&nbsp;Thanks for the quick reply.&nbsp;&nbsp;I added the +0 to the measure, but it returns all reports and seems to be overwriting the filter ???&nbsp;&nbsp;

Forum Discussion

Dax Outer Join

3 Replies

Related content

Natural Left Outer Join in DAX?

Left Outer Join through Relation ship joining

left outer join using dax, Multiple to Multiple

Power BI Pl 300 exam questions - Inner Join, Left outer Join and Right Join

left join dax