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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
v-amarsh
Employee
Employee

Table relationship ambiguity

Hello Everyone,

I am facing an issue with the tables relationships.

I have below existing tables structure in my data model

Onwer, Location, Status, Scope, Assessment Phase, Planning Phase, Migration Phase (All these 7 tables are calculated tables using system and server table fields)----> Servers Table (1 to Many) with single filter direction
Onwer, Location, Status, Scope, Assessment Phase, Planning Phase, Migration Phase (All these 7 tables are calculated tables system and server table fields)----> Systems Table (1 to Many) with single filter direction

I get an error when trying to activate another main table SystemServers with existing System and Servers main tables

Below are the relationships I need to activate.

Systems to SystemServers using 1 to Many with single filter direction which works fine.
Servers to SystemServers using 1 to many with single filter direction, which throws below error

 

IssueCapture1.PNG

Relationships image:

 

RelatiohsipDiagram.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I cannot disturb any of the existing relationships as it would change my report design and filter behaviour.

Could anyone help please?

 

Thanks in advance !! 

1 ACCEPTED SOLUTION

Hi @v-amarsh ,

 

Don't forget to mark the correct answer.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

3 REPLIES 3
MFelix
Super User
Super User

Hi @v-amarsh ,

 

What is happening is that the cross filtering between tables is detecting that the relationship you want to use already exists but trough some intermidiate tables (not a direct connection).

 

In this case depending on what you need to calculate you need to make use of the existing relations to make your calculations, using summarize or similar to create measures and get expected results, other option is to make use of USERELATIONSHIP, this will activate the inactive relationship when you want to calculate values between the two tables.

 

https://docs.microsoft.com/en-us/dax/userelationship-function-dax


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi MFelix,

Thank you for the reply and solution. My problem is solved with the USERELATIONSHIP function.

Also  LOOKUPVALUE function seems doing the same job as I've tested with this.

Thanks

Sharath Amarchinta

Hi @v-amarsh ,

 

Don't forget to mark the correct answer.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.