topic Re: how to compare 2 dates from different fact tables? in DAX Commands and Tips

how to compare 2 dates from different fact tables?

mocha911 — Mon, 05 Aug 2024 23:21:57 GMT

Hi All,

I am trying to write a DAX measure to get a flag which shows a service date (from Fact_Service table) greater than then upgrade date (from Fact_Member table). There is a Member dimension table between these two fact tables like below -

This is the output that I want to achieve in PowerBI-

There are multiple upgrades for each member and of course multiple services for each member in both fact tables.

But atm I only need the latest upgrade date to compare with all service dates for simplicity.

Any ideas how to do it? thanks

Re: how to compare 2 dates from different fact tables?

Anonymous — Tue, 06 Aug 2024 02:56:36 GMT

Hi mocha911 ,

I assume that the dimension table has 2 one-to-many relationships with 2 fact tables.

Here is my sample data.

Please try this measure

Measure 3 = IF( SELECTEDVALUE('Fact services'[Service Date])> SELECTEDVALUE('Fact member'[Upgrade Date]),1,0)

Result

Best Regards,

Wearsky

Re: how to compare 2 dates from different fact tables?

mocha911 — Tue, 06 Aug 2024 03:34:27 GMT

Thank you for the suggestion Wearsky,

I tried your measure but it shows an error - "Visual has exccede the available resources"

I am guessing the data is too big to do such an action? any other advices? thanks