WOW. This was the missing video, I was looking for. I did not know that one could change the direction to both to cross-filter, and this video showed me. I think I am not a total noob to PowerBI, but also, these little things are hard to get in the jungle of options, so anybody pointing me to such tips is highly appreciated. Thankgs Guy in a Cube!

High five Patrick! Just the way you explain new functionalities makes life so much simpler! Keep those interesting videos flowing! Thanks :)

What a great video! Not only have you shown when many to many relationships are most suitable and demonstrated how the same result was achieved before this feature was released, you have also encouraged why it is worth going the extra step to prevent such ambiguities. A separate video that actually demonstrates such an ambiguity and why it occurs could make it even more obvious, why it is worth going the extra step.

Usful and very informative video. And since am working in the oil & gas company, Many to Many relationship is extremely needed especially if we are working on wells,Reservoirs & fields data..etc for our analysis. Much thanks

As always, nice, funny and informative video!!! Thank you very much!

Thank you Patrick... Your explanations are always on point.!!

Great video you've got here Patrick.. I have been looking for a way around this many to many relationship issues cos its not always advised to use. I have a project on ground and i am faced with this challenge. I hope this solves it. I shall return with reports. Once again,Thank you

Great video man. If you do a good job modelling your data, you don't need to use a many to many relationship and only need to use one to many relationship. I never need to use many to many relationship because I spend a lot of time modelling my data.

Thank You for explaining it!!!

Correct approach. Nice video Patrick

I agree, cleaning up the data and reorganizing can have a big impact on performance as an added benefit.

You helped solve a huge problem for me. Thank you very much

Big thumbs up, thanks Patrick

Great video as usual ....just wondering how you made the final state metrics table putting both values from those two fact tables in single table.

As usual one more great idea..thanks

Hi Patrick...how do we decide when to make a “custom column” and when we need a “bridge table”. Love your work👌

Great video guyincub👏

Hello, I like the last approach to clean up the model. Many-to-Many would make me nervous about unexpected results.

Thanks for the video and the tips you give. My question is, How'd you do to get the Final_State_Metrics table? Since both metrics Population and Crimes Per Capita come from two different tables and not necessarily both of them have the same Geography key values

Hey Michael, so glad I found your channel. Subscribed and Shared! One thing I am having trouble with is dealing with 2 tables on 2 separate sheets inside a single excel file. In the web app I can't find a way to cross reference those tables. The real need I have is to create a slicer on a report that shows two different tables to filter the results to show only a particular project charters rows from those two separate tables. One table is time tracking and another is software cost tracking. I have spent 6 hours on it and I am just lost!

Yet another great video. Kudos!

Awesome tip Patrick

Thank you dear

Very good video thank you

Hi Patrick, I love your video, could you also explain how you did the last part; combing two tables with the same name?

Hey Patrick, so am I correct in thinking that merging a table is more efficient then solely relying on any relationship to give you the ability to merge columns from two different tables?

Hi Patrick - Great video about an aspect of PBI that confuses a lot of people! Thinking beyond the functionality of getting a M2M relationship to work for report creators... what are the performance implications of each option? My understanding is that when we use a bi-directional relationship, behind the scenes, the values of one of the tables get copied into the other (is that right?). I want to avoid my data model from getting too large and bloated, so which of the 3 options that you showed us (1. Bridge table with 2 x M2M relationships, 2. new M2M design, and 3. remodel data model to be specific to the visualizations) would be the most performant? My guess is that you'll say 'It depends...' ;-)

Hi Patrick, You mentioned in the video that you would show what you would do if you use a Bridge Table and one table is missing values. Please can you show? Thanks

Many to many shouldn't always be used but it can be extremely helpful when dealing with RLS management acess in my opinion

Hi, your explanation makes things simpler to understand. I have one ask, on dataset modelling if i have one dataset lets say global sales and i want to share the same dataset to country report creators with a requirement that they can view only their data while creating the dashboard for their management. Is it possible?

Most of the time I hear many to many is dangerous because of potential ambiguity in the model. Could you elaborate on different scenarios when they really make sense? Thank you. Great video

It's still not quite clear Patrick of what sort of problems the bidirectional relationship could cause so it'd be really nice if you can do a video on it..

Hi Patrick,In Power BI we have option to do lookup from vertical to horizontal without transpose the column

That's exactly what I need to check! You da man!

hey, nice video! I have some doubts about many to many, What if I have two fact tables, the first is for Sales, and the 2nd one is for Daily Call records, So I want to know how many calls they received per day, and from those calls, which ended up in a Sale? does that make sense? Will it work in the same way? or How I can combine both, without losing the details of each fact table?

Did you mention it's part of composite models capabilities?

Really helpful, Great Thanks

Good thank you

Thanks

Is it possible to have a refresh button in report itself for refreshing the dataset value?

Nice video, and Nice story , thank you. and hope more videos/stories about many-to-many relation ship. Power BI is good for talk about many-to-many relation ship, because easy/quick/visualize 😄

So how do you go about making that nice final dimension table you use in your slicers at the end?

Nice

I built several marketing data analysis reports where the end user wanted to see data periodically, so I joined tables on dates and all the tables had many to many relationship. Although it appears that my report is showing the right data but I've read many articles promoting against it. Is it normal to join on dates as many to many?

how can I maintain hierarchy between multiple role playing dimensions. for example: year from one distinct date table is used as slicer and if I select any year then it should filter countries in another slicer coming from another distinct table and selected country should filter relevant states coming from another distinct table. It is a case where multiple columns like year&country&state are concatenated to create unique key which is joined to another table with similar concat column but this is leading to many to many relationship. please help

Hi I got an issue I have 4 excel files (a, b, c, d) ppl joining company at different times (a, b, c, d) that uploaded into PBI. However I manage to merge Query on all of them.. But when I do calculated column, on two file for example b and c... It doesn't give me the correct answer... However I know that I used outer left join on column A to join the rest of files (b, c, d)... Basically all files have name.. Now I want to see the attrition between the files.

If there are more two keys which are same and unique in two tables which is the most efficient key to join them ? any video to clear my doubts is appreciated thankyou.

Did Patrick post his example report workbook?

Have you appended both table

How does the apply security filter in both directions work? Do I have to check it?

The problem I have with this is that the bridge table needs to be constantly updated. How can we just combine the two without causing issues?

What if you have two cost tables. One table has these fields: Cost Center, Product, and $s, with multiple products per cost center... because each cost center may allocate across multiple products. The second table has these fields: Cost Center, Cost Elements (eg. Salaries, Supplies, Travel, IT cost, taxes, etc...), and $s. Multiple Cost Elements per Cost Center. And you have Product Managers who want to know their $s by Product by Cost Element ?

Hey Patrick, if the database was updated and other states were inserted, will the bridge table get updated too? or should I update it manually each time there is an update to the database? Thanks

Can we get the PBIX file to understand how we transformed to Relationship (Final) from where we started? Thanks.

How could you define that it's LEFT, RIGHT or INNER JOIN in a relation?

Character In the video It's great, I like it a lot $$

I tried the many-to-many relationship connection between historical orders to a future orders table after the Sept update and my totals again are all wrong... I'm no sure why...

Is it logical to merge the 2 tables? I merge almost all of my tables, but I rarely see that as being proposed, I am new to Power BI, so just wondering if I am missing something.

Many to many saves the day, but do not understand the next level, is there more references on how to structure the merged? Tables.

Cool video!

I know this question is not related to the video, but I've posted on a few forums and no one is responding, so hoping you can help: Hi, My team and I are looking at switching reporting platforms and have been building concept pieces within Power BI, so far we have found everything and more, however, we are keen on making reporting not only dynamic from a user perspective but also from a production point of view. currently, we use SSRS and are able to have lookup tables with logic coded in and ready to be pulled through into multiple reports ensuring that where metrics are repeated any logic changes will be reflected across all reports at once. I've built a sample look-up table storing the DAX syntax needed for some of the measures used, then using the LOOKUP I'm looking to return the SUM() calculation string from the lookup table as a formula, however, it's pulling through as a string value. Is there a way of having this type of setup to make reporting more flexible between our warehouse and Power BI? (I guess what i'm looking for is something like Excels indirect function, using the string as an address) I hope this makes sense. Thanks

How to create dimension modeling

Nice video but can't publish to service..😔

Possible to buy the guyinacube t-shirt?

PowerBI portal video, please.

Sir where to practice dataset.. Dataset is not there

Poor video editing on the areas you screen grabs making it hard to follow.

that's good but, I think you miss a step - how did you know that the third table have all values from both facts ? - you made merge and then remove duplicates this is crucial step , you created new aggregate table i don't follow you skipping steps

i've tried many to many but the problem is i can't publish it.. anyone has the solution for it?

Everyone in is beating around the bush. No engineer would now give you an actual Step-by-Step tutorial. Just general talk, a little bit from here, a little bit from there ...

My measure totals are all wrong after I introduce a bridge table.

Shoulda said it.

This solution is very cheap, and the rest of the relationships with the other tables of the model? In the end it only solved one thing out of a thousand.

No I'm not having this. 6:50 into a 8:46 long vid and you start to give us a solution to all the issues you've been describing, then you say "combine the data into a distinct list" WTF does that mean? I thought it was APPEND if you wanted to do that? This is not a good solution video, it was useful as a thinking exercise but no solution as such was offered

Well, at the end i find the video pretty useless as there is ton of talking and no real explanation how to build such table

waste of my 10 minutes.