Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Greg_Deckler
Super User
Super User

Transitive Relationships

Not sure if this is news to anyone or not but was working on a private message regarding multiple date fields for a table and how to use a date table in that scenario since tables can only have a single active relationship. In this scenario you can use multiple date tables (one for each date field) or you can utilize the USERELATED() function. The first approach is quick and easy but the downside is that you cannot use a single date to slice across multiple visualizations.

 

Well, with my work on To Amend, Purpose and Bill – Text Analytics and the US Congress (

https://www.linkedin.com/pulse/amend-purpose-bill-text-analytics-us-congress-greg-deckler?trk=pulse_...

) I noticed that Power BI Desktop is pretty smart about transitive relationships, much smarter than the equivalent engine in Excel. So, I had a thought. I added a THIRD date table and related my two other date tables to this date table. Now I suddenly had a single date table that I could use to slice across multiple visualizations.

 

Having issues in Edge and IE11 inserting an image, but here is a text diagram.

 

                          Requests
  Created*                                   Implemented* (Columns)
   |                                                                    | 
   |                                                                    |
   1  1                                                              
BasicCalendarEnglishCreated  BasicCalendarEnglishImplemented
   *                                               *
   |                                                |
   |                                                |
   1                                              1
          BasicCalendarEnglish

 

 

Note that I tend to use DateStream from the Azure Data Marketplace for my date tables. Great, free date table source.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
1 ACCEPTED SOLUTION

Correct, I should have noted that, the second relationship that you make to the "BasicCalendarEnglish" will display as inactive, but it DOES work when you use it in the visualizations. So, I created three visualizations. The first uses the "Created" date and count of "Requests" from the Requests table. The second uses the "Implemented" date and count of "Requests" from the Requests table. The third visualization, a slicer, uses any field from the BasicCalendarEnglish table. Clicking on the slicer filters both of the other visualizations even though the one relationship going from BasicCalendarEnglishCreated" displays as inactive in the relationship view.

 

Magic.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
ALeef
Resolver II
Resolver II

Did you get this to work?  What does your relpationships model look like?  Mine wouldn't let me create the relationships between the overarching calendar and the individual ones.  I could make one, but the second one went inactive immediately.

 

 

I finally got to this work

 

Brilliant. Thanks for this

 

I did it with an order Date Date and Invoice Date

 

I was surprised it worked but it does

 

The key though is to make the buttom calendar's 1 to many not many to 1. (you did note this above very clearly but I ddin't really get this at first as the opposite accepted the join)

 

Edit. Actually it didn't work. The active joined produced the correct figures but the inactive join didn't. I will try again tomorrow but I can't see how this can be done without Power BI allowing a many to many join (Im unsure why they have not done this. Give a warning but allow it. But at least they have allowed outer joins when some BI solutions do not or only partially do. I would not even consider a BI solution that does not allow all 4 join types)

 

edit. Got this to work as follows

 

http://community.powerbi.com/t5/Desktop/Linking-2-or-more-dates-to-one-Master-Calendar-USERELATIONSH...

 

 

 

 

Correct, I should have noted that, the second relationship that you make to the "BasicCalendarEnglish" will display as inactive, but it DOES work when you use it in the visualizations. So, I created three visualizations. The first uses the "Created" date and count of "Requests" from the Requests table. The second uses the "Implemented" date and count of "Requests" from the Requests table. The third visualization, a slicer, uses any field from the BasicCalendarEnglish table. Clicking on the slicer filters both of the other visualizations even though the one relationship going from BasicCalendarEnglishCreated" displays as inactive in the relationship view.

 

Magic.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Pretty cool to find out!

 

I played around with it in my data, and it ended up not being what I needed, but still a good thing to know!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.