topic Multiple Dates with Inactive Relation in DAX Commands and Tips

Multiple Dates with Inactive Relation

_Regina — Fri, 10 May 2024 18:26:54 GMT

Hi everyone,

In my power bi data model , I have a table 'Onboarding Request' which has multiple date columns. This table has active relation with date table based on Apllication_Start_Date_Dim_Sysid, all other date such as Referal_date_dim_sysid has inactive relation.

What I am tryin to do is provide access to the data in a table visual and have all the dates associated with Onboarding_ID listed in the visual.

I have used th efollowing DAX for Referal_date as an example:

_Referral_date_formatted =

MAXX(

FILTER('Onboarding Requests','Onboarding Requests'[REFERRAL_DATE_DIM_SYSID]=MAX('Onboarding Requests'[REFERRAL_DATE_DIM_SYSID])),

RELATED('Calendar'[Date])

)

But as you can see in the screenshot below, I need referal_date formatted to give me the output based on Referal_date_dim_sysid, in this case I need 02/23/2021 because Referal_date_dim_sysid is 20210223, but what I get is 3/14/2021 which is the max date associated with the Onboarding ID. How can I modify the DAX to achieve the correct date.

Thank you in advance

Re: Multiple Dates with Inactive Relation

DataNinja777 — Sat, 11 May 2024 10:04:54 GMT

Hi _Regina

It sounds like in this case, the best stragegy for you is to have your calendar table and your multiple dates fact table as disconnected tables. When the multiple dates fact table requires analysis of duration like period from and period to, you won't be able to perform your intended analysis if the relationship is created between the calendar table and your fact table, and they need to be disconnected tables for you to perform your intended analysis. Or else, if the analysis you require doesn't involve duration calculation, you can use Userelationship function to activate your inactive relationship in a specific measure.

Best regards,

Re: Multiple Dates with Inactive Relation

Anonymous — Tue, 14 May 2024 05:44:21 GMT

Hi _Regina ,

Hi @VenuDakoju ,

Whether the advice given by DataNinja777 has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.