Transitive Relationships

[Greg_Deckler]

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.

[ALeef]

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.

[RJ]

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...

[Greg_Deckler · 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.

[ALeef]

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!