Forum Discussion
Helper IHow to filter on a field shared between tables
I have 3 tables for three products. Each table contains a few columns which are similar about all three 3 tables, they are region and type. Each table also has a date column.
I have created a date table which I use to do date filtering, this works well.
On a PowerBI page I have 3 matrix tables, one for each of the different tables. I can slice on the dates using the date table I created.
What is the best way to be able to filter on the region and the type? Is it the same approach as creating a constant date table? Is best practice to create a new table for each field you want to filter on and then create a relationship back to the filter table?
I thought about merging the 3 tables together however each table is already over 4m rows long, adding them together makes it over 12m rows in length. I worry what this would do to performance.
Keen to hear best practice.
4 Replies
- amitchandak
Super User
User7664 , One option is merge these tables into one.
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
The second is to create a Date Table and common region table and any other common table required and use the tables
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
refer- https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/
User7664Thanks Amit,
I watched your video, very helpful! I wouldnt have thought of using distinct and union. I will work on this tomorrow.
I do have two questions tho.
- How would a many to many relationship work? Would it not work at all in this case? In your example if you did a many to many relationship on CityID would it not work?
- Are there any cleaner options (that dont involve merging the tables)? If you have 5 or so items that are common on all the tables that is 5 additional tables that need to be created. It will just end up been quite messy.
Thanks again. Great video and great help!
v-jingzhangCommunity Support
Hi User7664
There are some videos about many-to-many relationships that may be helpful.
Is MANY TO MANY, too many? | Power BI many to many relationships explained - YouTube
Looking at Power BI Many to Many - YouTube
Document:
Many-to-many relationships in Power BI Desktop - Power BI | Microsoft Docs
In my practise, sometimes many-to-many relationship will cause some confusions in calculation so I will check the result manually to ensure it is working well when I apply it in my model. What's more, you need to pay attention to the cross-filter direction in relationships. It will also influence the result.
Usually the Dim tables (or bridge tables) only contain distinct values and will not occupy too much data size.
- parry2k
User7664 Yes as a best practice, you need to create a common dimension table and link with other tables and then use a dimension table (Like date table) in the slicers.
Read this post, it will help.
Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
