cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
INeedSomeHelp
Frequent Visitor

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

Accepted Solutions
Super User II
Super User II

Re: Power BI - ambiguous relationships

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

Re: Power BI - ambiguous relationships

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
INeedSomeHelp
Frequent Visitor

Re: Power BI - ambiguous relationships

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?

Super User II
Super User II

Re: Power BI - ambiguous relationships

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

INeedSomeHelp
Frequent Visitor

Re: Power BI - ambiguous relationships

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

 

Super User II
Super User II

Re: Power BI - ambiguous relationships

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
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors