Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
_Regina
Helper I
Helper I

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.

 

_Regina_0-1715359371563.png

 

Thank you in advance

 

1 ACCEPTED SOLUTION
DataNinja777
Super User
Super User

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,

View solution in original post

2 REPLIES 2
v-tangjie-msft
Community Support
Community Support

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
Super User
Super User

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,

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.