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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
kky1
Helper II
Helper II

Help adding column from another table with many to many relationship

I am stumped trying to add a column from one table to another which have a many to many relationship.

The two tables are for a calendar visual - one table has date/time start times and the other has date/time end times for the calendar.

 

I tried creating an ID table with one to many relationships to both of these tables, but the calendar visual doesn't recognize the relationship between the times tables and won't work. 

 

Ideally, I need one table with the start times end times. When I tried merge tables, it shows Table link. Is there a way to merge only the end times to the start time table in a way that is accurate by id and day name?

 

I've looked at relatedtable function but can't seem to get it to work the way I want. 

 

Here is the data inside the two tables:

 

Start Time table:

calendar start timescalendar start times

End Time Table:

calendar end timescalendar end times

 

All of the columns in both tables after the start/end time are calculated columns to get the current date for the day of the week. 

 

I tried linking the two tables on id and day name, but the calendar then shows multiple entries for every employee with every start and end time on the calendar (not correct data).

 

If anyone could assist with which DAX function I can use to achieve this, I'd be most grateful.

 

1 ACCEPTED SOLUTION
kky1
Helper II
Helper II

Hello - I figured out a solution for this which I'll post for anyone such as myself who is still in the learning stages of DAX. I'm sure it's not the most efficient way, but it worked for me.

 

I did a merge tables action which created a table with all of the data. In the new Merge table, I redid the calculations to gather the current weekday and created a date/time field for start and end dates. This created a table with a huge amount of duplicates where the start day name and end day names didn't match due to the many to many relationship.

 

So, I created a new column within the table to compare the two day names with "Yes" if they matched and "No" if they didn't. Then I just created a filter on my visual page for only those with a "Yes". This did the trick. Below are screenshots in case this helps anyone. 

Merged table with many values from the start time and end time table;

pic1.JPG

 

Added column to merged table to check if the day names matched.

 

pic2.JPG

Finished calendar now shows one entry per employee per day.

 

pic3.JPG

 

 

View solution in original post

1 REPLY 1
kky1
Helper II
Helper II

Hello - I figured out a solution for this which I'll post for anyone such as myself who is still in the learning stages of DAX. I'm sure it's not the most efficient way, but it worked for me.

 

I did a merge tables action which created a table with all of the data. In the new Merge table, I redid the calculations to gather the current weekday and created a date/time field for start and end dates. This created a table with a huge amount of duplicates where the start day name and end day names didn't match due to the many to many relationship.

 

So, I created a new column within the table to compare the two day names with "Yes" if they matched and "No" if they didn't. Then I just created a filter on my visual page for only those with a "Yes". This did the trick. Below are screenshots in case this helps anyone. 

Merged table with many values from the start time and end time table;

pic1.JPG

 

Added column to merged table to check if the day names matched.

 

pic2.JPG

Finished calendar now shows one entry per employee per day.

 

pic3.JPG

 

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors