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, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
osinquinvdm
Advocate II
Advocate II

Issue with many-to-many relationship

When the users are looking at event details they would like a simple selector for filtering on today/this week/this month/this year/all.

 

My idea is to create a column called "temporality" that associates each date with each of the 5 temporalities that would apply to it.

 

I will end up with a many-to-many relationship because each date can have several temporality (up to 5) and each temporality can be associated with several dates (up to 1000+ for the “all” temporality)

 

I thought I could create a simple lookup table called Calendar with all the dates that are present in the data.

This dataset has 2 columns: date and temporality. Each date is repeated across several lines(5 times for the today’s date, 4 times for the rest of the week, 3 times for the rest of the month…).

 

To bridge the Event table and the Calendar table I created an AllDates table that simply contains every date.

 2017-03-02 16_54_35-calendar - Power BI Desktop.png

I can show the temporalities associated with each date and the events associated with each date.

 

It seems I can even filter on temporalities and only show the events associated with it.

 2017-03-02 17_24_24-calendar - Power BI Desktop.png

But Power BI won’t let me show the temporalities associated with each event on the same grid.

 

How to solve that relationship issue?

 

NB: I guess I could pivot the Calendar table, then merge it with the Event table, then unpivot the temporality columns but then the table would become massive. That's why I'd rather go with a lightweight lookup table that would be used for the dates only...

 

NB: I'm not sure what differnce it actually makes but I set Cross filter direction to "both" for the 2 relationships

2 ACCEPTED SOLUTIONS

For this to work, you will have to use a measure in your grid. Unless you have a measure, you will keep getting this error message. 

Try creating a simple measure like 

Test = countrows(EventTable)

 

Then add Temporalitie, Event and the Test measure to a grid, and you should get the result. 

View solution in original post

Hey, you are right! That tricks work. It's like if we were forcing one of the table to be the fact table and then tadam it knows how to use the lookup tables to get the aditional details.

 

Thanks for the trick @SqlJason, you rock!

View solution in original post

4 REPLIES 4
MalS
Resolver III
Resolver III

When you say that "Power BI won’t let me show the temporalities associated with each event on the same grid" what happens? Do you see an error on the grid? 

 

I thought bi-directional filtering would let this work. Here's a great explanation about it: https://www.sqlbi.com/articles/many-to-many-relationships-in-power-bi-and-excel-2016/ 

 

 

when I try to create a grid with both events and temporality it gives the relationships error.

2017-03-03 10_43_36-.png

Which is strange, you are right, since it seems able to filter out events based upon temporality, so the relationship seems to be there somehow,

For this to work, you will have to use a measure in your grid. Unless you have a measure, you will keep getting this error message. 

Try creating a simple measure like 

Test = countrows(EventTable)

 

Then add Temporalitie, Event and the Test measure to a grid, and you should get the result. 

Hey, you are right! That tricks work. It's like if we were forcing one of the table to be the fact table and then tadam it knows how to use the lookup tables to get the aditional details.

 

Thanks for the trick @SqlJason, you rock!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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