Tricky Slicer Situation

Hi Anonymous

There are two approaches I can think  of:

1.  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
2.  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