Forum Discussion
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:
And returns this result:
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.
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?
Community ChampionYes, 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:
And returns this result:
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.
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
- edhans
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.