Forum Discussion
How to apply a slicer from one table to another table
Hi there, I am new to Power BI and do need some help on how to link a slicer from TableA to TableB. Basically, the slicer is coming from Type column from TableA. What I need is when the user select an option as FY1 or FY2, TableB will be filtered by matching their ID. For example, when FY1 is selected, the TableB will be filtered as
1 10
1 20
2 30
2 10
Join TableA and TableB does not work, as ID are not unique is either A or B.
The goal to get the sum of 10,20,30,10 when FY1 is selected, or 5,5,20,10 when FY2 is selected.
Any help is greatly appreciated.
23 Replies
Is there any reason you can't de-dupe table A so that the ID value is unique?
Anonymous you should create a new ID table that would be a bridge table that would contain the unique ID numbers. That becomes your DIM table. You then relate that (it isn't a join, it is a filter relationship) to each of the ID columns in your two other tables. Then, use the ID from the bridge table in your slicer and in all visuals where the ID value would be used. You should, in fact, hide the ID field in the two other tables.
All of your visuals would just work at that point, and any related DAX becomes much easier to deal with. See the link below for more detailed guidance on avoiding Many to Many relationship, which is what would have to happen if you don't create the bridge table. To create the bridge table, you can do this easliy in Power Query by creating a reference from your FACT table, remove all columns but the ID column, then right-click on ID and "remove duplicates".
Microsoft recommends minimizing use of both Many-to-Many and Bi-Directional Relationships. In other words, unless you are a DAX expert, find another way to remodel your data to conform to a Star Schema and don't use these two features. I avoid them both at all costs.
Microsoft Guidance on Many-To-Many Relationships
Microsoft Guidance on Bi-Directional Relationships
Microsoft Guidance on Importance of Star Schema
Building a Bi-directional relationship can fix it easily.....
This is the solution in a clean, kind of easy way
Anonymous Are the two tables related to one another on ID? If so that should happen automatically.
Creating a bi-directional relationship can slow the performance of your report if the data is huge.
You can create a bridge between the two tables using the filter that you need then create a relationship from it.
that way the two tables will be related and filtered according to your selection.
Hope this helps.
You can active this feature , thin click on table one and change the interactios on the second table
But make sure that thier is an active realtionship between the two table
Best WishesFor whatever reason, this unanswered thread got revived. This kind of issue, filtering a fact by another fact, is done with measure filters:
https://www.sqlbi.com/articles/syncing-slicers-in-power-bi/
For OP particular example, the solution
Given following starting tables
TableA
ID Type 1 FY1 2 FY1 2 FY1 3 FY2 4 FY2 TableB
ID Amount 1 10 1 20 2 30 2 10 3 5 3 5 4 20 4 10 Solution steps
1) create a dimension with IDs from both tables (facts)
TableDim = // Creating in DAX but could be done in PQ as well DISTINCT( UNION( SELECTCOLUMNS( TableA, "ID", TableA[ID] ), SELECTCOLUMNS( TableB, TableB[ID] ) ) )TableDim
ID 1 2 3 4 2) create the single direction filters between dim -> facts
3) Now create a measure that will be meant to be used filter your visuals such that slicer selections on TableA will impact visuals using your dimension columns.
TableA Filter = IF( NOT ISEMPTY( TableA ), 1 )4) Now add this measure to filters of your visual and set to 'Not is blank'
Note on the subsequent scenario OP introduced with different tables
The overall pattern does not change. With new tables/columns: State = ID, EventFY = Type.
So, same steps of: 1) create a dim of distinct State, 2) relate your dim to your facts keeping with single direction filters (star schema), 3) create your visual filter measure, 4) apply to your visuals (ensuring that you are using the dimension as the axis of your visual
On using bi-directional filter
This can be accomplished with bi-directional filtering, which can be okay to use IF your model is simple and will stay simple. With three tables (1 dimensions, 2 facts), bi-directional is safe. But all it takes is to add another dimension to add ambiguity. sqlbi's authoritiative article on this talks through this exact scenario:
https://www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/
This is ambiguous with the flagged bi-directional filter:
because the Date dimension can now filter Purchases fact through TWO different paths (as noted by path #1 and path #2):
Even if PBI accepts your ambiguity (sometimes it just errors out your relationship), you have now introduced messy complexity into how calculations are evaluated. The article I referenced goes into this in detail if you want further explanation on this point.
