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
Anonymous
Not applicable

Power BI - ambiguous relationships

Hi everyone,

Let's suppose I have my only fact table (Sales) which is directly connected to the table Order . Then this table Order is divided into 2 exclusive "paths": 

  • the one for the orders made online
  • the one for the orders made in a store

These 2 hierachies are then connected to a table Province (where we have geographic informations). 

The problem is that Power BI doesn't authorize me to have active relationships from both hierarchies to the table Province because otherwise, there would be ambiguous relationships.

How can we manage this problem without having to create a second fact table (i.e. : sales for online and sales for store).

Thanks in advance ! 

1 ACCEPTED SOLUTION

Yes, you can do just about anything with an inactive relationship you can with an active relationship when it comes to measures. You just have to use CALCULATE or CALCULATETABLE to get there.

 

 

USERELATIONSHIP Measure = 
CALCULATE(
    MAX(Data[Name]),
    USERELATIONSHIP(Data[Field],'Table'[Field])
)

 

That is based on this model:

2020-03-27_13-14-38.png 

And returns this result:

2020-03-27_13-14-18.png

 

MAX() is a way to convert a column to a scalar value that measures require. You could use VALUES() too if you 100% knew you'd only get one result.

 

I did see your model. Your issue is your SalesPerson table is 1 to many to your Storeor... table, and also 1 to the Store table. There is no way for DAX to know truly what to do or which path to take if you go from Store to Storeor... (the name was truncated 🙂

By using USERELATIONSHIP it tells it exactly how to behave and it ignores the other stuff.

 

Make sense?

 

As a side note, you can use LOOKUPVALUE() to get to unrelated tables. You can read through this thread this morning on how to do it. With or without a relationship, LOOKUPVALUE() will work though it is a pretty inefficent tool to use, but depends on the model size. As noted in my sig, I prefer to model in Power Query which is designed for this, and let DAX analyze data vs constructing tables and data. But it is always use the right tool for the job, and I am viewing your job through a peephole and don't understand the overall project.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

5 REPLIES 5
edhans
Super User
Super User

You need to share more info, like a screenshot of your model. I suspect you have a bunch of bi-directional relationships, which should be turned off. That might fix the problem, but without more data, hard to say.

 

And Inactive Relationships can still be used. You just need to use the USERELATIONSHIP() modifier in CALCULATE().

 

 

 

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

Thanks for your answer,

Is there a way that i send you the screenshot of my data model privately? (edit: I sent you a private message with the data model)

About the USERELATIONSHIP() function I saw that this can be used to simulate active relationships and so make computations but is it also possible to get text field from that function? Suppose I have a table StoreOrder which is linked to the table Store. There is a relationship via the StoreID (inactive in this case) and I would like to retrieve the column Name (which is a String) for the associated StoreIDs. How can I achieve that?

Yes, you can do just about anything with an inactive relationship you can with an active relationship when it comes to measures. You just have to use CALCULATE or CALCULATETABLE to get there.

 

 

USERELATIONSHIP Measure = 
CALCULATE(
    MAX(Data[Name]),
    USERELATIONSHIP(Data[Field],'Table'[Field])
)

 

That is based on this model:

2020-03-27_13-14-38.png 

And returns this result:

2020-03-27_13-14-18.png

 

MAX() is a way to convert a column to a scalar value that measures require. You could use VALUES() too if you 100% knew you'd only get one result.

 

I did see your model. Your issue is your SalesPerson table is 1 to many to your Storeor... table, and also 1 to the Store table. There is no way for DAX to know truly what to do or which path to take if you go from Store to Storeor... (the name was truncated 🙂

By using USERELATIONSHIP it tells it exactly how to behave and it ignores the other stuff.

 

Make sense?

 

As a side note, you can use LOOKUPVALUE() to get to unrelated tables. You can read through this thread this morning on how to do it. With or without a relationship, LOOKUPVALUE() will work though it is a pretty inefficent tool to use, but depends on the model size. As noted in my sig, I prefer to model in Power Query which is designed for this, and let DAX analyze data vs constructing tables and data. But it is always use the right tool for the job, and I am viewing your job through a peephole and don't understand the overall project.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

Thank you so much man, it helps a lot !

I now better understand the utility of the USERELATIONSHIP() function. 

I may have other questions later but so far, it seems ok

 

Great. The key to remember is USERELATIONSHIP cannot create them, it just activates them, and deactivates any other relationships between those tables for that measure alone.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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.