Forum Discussion
Helper IIISlicer: Slice two tables with the same column values
I have two tables:
Table 1: IDs with actual KPI data sorted by month
Table 2: IDs with plan KPI data sorted by month
These two tables are conntected by a MonthNum column.
If I add a slicer to my dashboard, it only slices one table, but not the other.
I tried adding another table only with ID, but I can't connect both tables two the same column, it says two many active relations.
Unfortunately the way you have done things is not the best one so please think of exploring the nature of the relations between tables before you implement your model. A many-to-many model is not a good design, it is rather a lazy and unthought design. Essentially you would be using it because you don't want to have to spend the time thinking about the true relationship between the two entities. There are a significant number of relationships between real-world entities that are always one-to-many. If you make all of your relationships many-to-many, this means the following downsides just off the top of my head:
- You are adding a number of unnecessary tables to your database to act as intersection entities.
- Querying data from your database will require more table JOIN operations, which will impact performance.
- Additional code/logic is required to maintain the intersection entities.
- In cases where the relationship between two entities *is* one-to-many, you have just created a potential error condition (i.e. a child mistakenly having two parents).
The proper answer is to spend due diligence during the design process. Please try doing so in the future!
8 Replies
- amitchandak
Super User
tuncay , share relationship diagram, I doubt one relation is inactive.
tuncayYes, since there is already a relation between actual and plan because of MonthNum column, the relation between ID and plan gets inactive.
- goncalogeraldes
Hello there tuncay ! Is the Cross-filter direction set to "Both"? Nevertheless I think you need to either create a calcuted table for the month table or a date table in Power query and connect both tables to this existing new table.
Here's a good example of how to do it:
https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns
Hope this answer solves your problem! If you need any additional help please tag me in your reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍
Thanks!
Best regards,
Gonçalo Geraldes- tuncay
No, I need to continue like I've done already. And yes the cross-filter direction is set to "both"...
- goncalogeraldes
Unfortunately the way you have done things is not the best one so please think of exploring the nature of the relations between tables before you implement your model. A many-to-many model is not a good design, it is rather a lazy and unthought design. Essentially you would be using it because you don't want to have to spend the time thinking about the true relationship between the two entities. There are a significant number of relationships between real-world entities that are always one-to-many. If you make all of your relationships many-to-many, this means the following downsides just off the top of my head:
- You are adding a number of unnecessary tables to your database to act as intersection entities.
- Querying data from your database will require more table JOIN operations, which will impact performance.
- Additional code/logic is required to maintain the intersection entities.
- In cases where the relationship between two entities *is* one-to-many, you have just created a potential error condition (i.e. a child mistakenly having two parents).
The proper answer is to spend due diligence during the design process. Please try doing so in the future!
- tuncay
can anybody else help?
