How to filter on a field shared between tables

[User7664]

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.

[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.⚡

[amitchandak]

@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

https://youtu.be/Bkf35Roman8

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/

[User7664]

Thanks 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. 

1. 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?
2. 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-jingzhang]

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.