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
Ashish9201
Helper I
Helper I

Inactive Relationship

Hi All,

 

I have below tables which has active and inactive relationship assigned, Highlighted Relation is an inactive relationship between dim date and timeentry start date. I am doing a comparison between Forecasted hours(From table bookable resource) and Actual Hours(From table Timeentry) here.

Ashish9201_0-1661248157012.png

 

Below is the result which i am getting currently while visualizing data in table. I am getting correct value in forecasted table since this has active relation with dim date table, however i am not getting correct value for actual hours since the table relation is not active.

Have tried using this formula to get actual hours worked : 

Actual Hours Worked = CALCULATE(SUM(msdyn_timeentry[Duration (Hours)]),USERELATIONSHIP(msdyn_timeentry[Start],'Dim Date'[Date])). Can someone help me on this?

Ashish9201_1-1661248342706.png

 

Regards,

Ashish Gupta

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Ashish9201 , Many to Many bi- directional indexes are creating loops, so relationships are not valid and inactive. Please correct the model using bridge tables 

 

https://www.seerinteractive.com/blog/join-many-many-power-bi/

https://amitchandak.medium.com/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-solutio...

View solution in original post

2 REPLIES 2
Ashish9201
Helper I
Helper I

Hi @amitchandak ,

 

Let me explain the relationship of tables here, hope this can help you in resolving my difficulty

Ashish9201_1-1661250184317.png

 

1 relation is inactive between "Dates" Many to many (table 1 and 2)

2 relation is inactive between "Projects" Many to many (table 1 and 2)

3 relation is active between "Resources" Many to many (table 1 and 2)

4 relation is active between "Dates" Many to one (table 2 and 3)

5 relation is inactive between "Dates" Many to one (table 1 and 3)

amitchandak
Super User
Super User

@Ashish9201 , Many to Many bi- directional indexes are creating loops, so relationships are not valid and inactive. Please correct the model using bridge tables 

 

https://www.seerinteractive.com/blog/join-many-many-power-bi/

https://amitchandak.medium.com/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-solutio...

Helpful resources

Announcements
Europe Fabric Conference

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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