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

USERELATIONSHIP gives a False Warning

I am studying DAX etc. in the good book Beginning Power BI from Apress. There is an exercise that ask to create an Inactive Relationship as shown below.

 

Inactive Relationship.jpg

Then I'm asked to add the measure under the Store table:

Store Count := CALCULATE(DINSTINCTCOUNT([StoreKey]), USERELATIONSHIP(Store[OpenDate], 'Date[DateKey]))

Measure Chapter 6.jpg

When I do the Pivot Table, it matches the one in the book but a message appears above the Field List (shown above). Clicking Auto-Detect or Create does not help at all. The message appears as soon as I add a field from the Geography table (the Continent field).

 

I contacted the author of the book and he replied: "You can dismiss that warning, When using the relationship in the DAX it gives a false warning. Just make sure the values are correct in the pivot table."

 

I thought I would ask for a second opinion. It is confusing that this DAX expression doesn't really work by returning a ... False Warning.

 

Any help would be appreciated.

 

Daniel

8 REPLIES 8
Super User
Super User

Re: USERELATIONSHIP gives a False Warning

Hi,

Is there a relationship from the Geographykey column of the Store Table to the Geography column of the Geography Table?  If not, create that relationship.  If the message persists, then share the link from where i can download the PBI file.

Super User
Super User

Re: USERELATIONSHIP gives a False Warning

Hey,

 

you have to be aware of the fact that the pivot table interface that you are using in Excel and the DAX execution engine are 2 separate things.

 

The warning is issued by the pivot table interface as it realizes that there are no "active" relationships between the tables.

 

Your statement "...  DAX expression doesn't really work ..." is not precise, a better statement would be "unfortunately the Pivot Table interface isn't smart enough to discover that a relationship is established between the tables through the usage of the measure" :-)

 

Hopefully this is what you are looking for.

 

Regards,

Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
DLamarche Frequent Visitor
Frequent Visitor

Re: USERELATIONSHIP gives a False Warning

Hi Tom,

Thanks for your help.

So if there is an inactive relationship Pivot isn't smart enough to discover that a relationship is established between the tables through the usage of the measure. Whan about the inactive relation ship then. Just asking here  8-)

As a junior is this domain one would think that the relationship established through a measure and the inactive relationship would suffice, no?

Thanks again Ben.

 

Daniel

DLamarche Frequent Visitor
Frequent Visitor

Re: USERELATIONSHIP gives a False Warning

Hello Ashish.

Thanks for your interest in my question. Below is the screenshot of the relationships.

It appears that there is a relationship between the two tables (Geography and Store)

Relatioship between Store and Geography.jpg

Again than you for your interest.

If you or anybody else want me to send the Excel workbook (with Model & Measures) then it's fine.

Super User
Super User

Re: USERELATIONSHIP gives a False Warning

Hey @DLamarche ,

 

there is an easy part in your question, namely

" ... So if there is an inactive relationship Pivot isn't smart enough ..."
Yes the Pivot Table Interface isn't smart enough :-)
But please be aware that the Pivot Table interface is not the same as Power Pivot, the SQL Server Analysis Services Tabular engine running inside Excel. And this also does not mean that the Pivot Table interface is bad, it's just this specific topic where it lacks some "smartness" in combination with Power Pivot.

 

The rest of your question is more complex and not that easy to answer, so maybe my approach to answer your question might be lacking some bits and pieces, for this reason I recommend this book, as it is about the modeling (there is also some DAX) of tabular data models : https://www.amazon.com/Analyzing-Power-Pivot-Business-Skills/dp/150930276X/ref=sr_1_5?keywords=Marco...

 

Now, my approach to answer your question :-)

Basically, it's always good to have relationships between tables, as relationships are physically supporting the concept of "Filter Propagation", selected values (selected on the one side of the relationship) are propagated / are flowing to the many side of the relationship.

Sometimes it's necessary to establish more than one relationship, e.g. between a date table (Calendar table) and a sales table that contains two date columns: orderdate and deliverydate. But, there can be just one active relationship. For this reason we have to use the DAX function USERELATIONSHIP(...), this function activates a physical existing but inactive relationship. There is no golden rule which relationship should be active and which one(s) become inactive, this depends on the overall usage of the data model.

There is another reason why just an inactive relationship can be established, and this is the case when a circular reference between tables will become existent. If you try to make the relationship active this message will pop up:

image.png

So, you just can create an inactive relationship that will be activated by USERELATIONSHIP().

There are also DAX functions that establish a soft relationship between tables like TREATAS, but describing this in detail would be beyond the scope of this thread :-)

Just be aware of this:

  • active relationship are good
  • inactive relationships can be activated
  • 1-to-many relationships are better than many-to-many
  • Soft relationships should make you reconsider your data model :-)

Regards,

Tom

 

 

 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Super User
Super User

Re: USERELATIONSHIP gives a False Warning

Hi,

Share the link from where i can download your Excel workbook.

DLamarche Frequent Visitor
Frequent Visitor

Re: USERELATIONSHIP gives a False Warning

Hello @Ashish_Mathur ,

 

Sorry for my ignorange but I'm not sure what you mean by share the link.

Do you mean through OneDrive?

Does it have to be a .zip file?

 

Thanks

Highlighted
Super User
Super User

Re: USERELATIONSHIP gives a False Warning

Yes, through OneDrive/Google Drive

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 42 members 1,116 guests
Please welcome our newest community members: