Forum Discussion

_Regina's avatar
_Regina
Helper I
2 years ago
Solved

Multiple Dates with Inactive Relation

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

 

  • DataNinja777's avatar
    DataNinja777
    2 years ago

    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,

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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. 

  • DataNinja777's avatar
    DataNinja777
    Super User
    2 years ago

    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,