Forum Discussion
Transitive Relationships
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.
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.
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.
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!
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
