Forum Discussion
Tricky Slicer Situation
I have a table that looks something like this: A company can have 2 managers. They can be the same person, have only one manager, or mix and match managers. I'm currently stuck in a situa...
Hi Anonymous
There are two approaches I can think of:
- In the query editor,
- add an index column to your original fact table
- right click on the original query and click reference
- from the new query, remove all other columns except the index and managers column
- select index column, right click and click unpivot other columns
- load the new new and old queries into the model
- create a bidrectional relationship between the two tables in your model with index as the relationship key
- use the values column from the new table in the slicer
- Using DAX
- create a disconnected calculated table which is the union of managers column (disonnected = must have no relationship with your fact table)
ManagersTable = VAR M1 = SELECTCOLUMNS ( Table, "Manager", Table[Manager1] ) VAR M2 = SELECTCOLUMNS ( Table, "Manager", Table[Manager2] ) RETURN UNION ( M1, M2 ) - create a measure to be used as a visual filter
ManagerFilter = VAR Manager = SELECTEDVALUE ( ManagersTable[Manager] ) RETURN IF ( ISFILTERED ( ManagersTable[Manager] ), CALCULATE ( COUNTA ( Table[Name] ), Table[Manger1] = Manager ) + CALCULATE ( COUNTA ( Table[Name] ), Table[Manger2] = Manager ), 1 //return 1 if no item in ManagersTable[Manager] column is selected
) - use the manager column from the calculated table in the slicer
- create a disconnected calculated table which is the union of managers column (disonnected = must have no relationship with your fact table)
- In the query editor,
danextian I have everything built but I'm confused on how to apply the measure. Am I applying it to the table and making sure it's set to 1 or 0?
Select the visual you want to be filtered by the new measure. In filters pane, add the measure to Visual filters and set it to greatee than or equal to one as the measure will return one or more if the new column is filleted or either of the two calculates returns a value. It is important that the new table is disconnected.