Forum Discussion
How to apply a slicer from one table to another table
For 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.