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
DLamarche
Helper I
Helper I

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

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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

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

 

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Yes, through OneDrive/Google Drive


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.