Tricky Slicer Situation

[Anonymous]

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 situation where I'm using 2 slicers for each manager column. What I'm trying to figure out is how to have that in one slicer. So the slicer would just be:

Fred

Lisa

Bob

Tim

So if for instance I select Fred, these are the rows and revenue totals that would be returned:

So anywhere where Fred's name appears is what I would be looking for.

This is rather tricky because what I'm also expecting in the end is for the slicer to control everything else on the page. I have other tiles that show how many clients each person manages, what their retention is, etc. and right now I have 2 tables for everything which doesn't help because Fred would have 1 revenue total in one table and 1 revenue total in the other table without a way to sum both of those values to get an overall for Fred. Is this possible to set up?

[msornakumar]

Hi,

  Can you please try the below approach of creating two measures instead of two tables ? From my testing it gives proper results as expected. Not sure it will cater all your scenarios. Please let us know.

Relationship

Revenue Manager1 = CALCULATE(SUM(Data[Revenue])) 

Revenue manager2 = CALCULATE(SUM(Data[Revenue]),USERELATIONSHIP(Manager[Manager],Data[Manager2]) )

Total Revenue = if(ISBLANK([Revenue Manager1]),[Revenue manager2],[Revenue Manager1])

 

[danextian · Accepted solution]

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

[Anonymous]

@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?

[danextian]

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.