Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • danextian's avatar
    danextian
    8 years ago

    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

4 Replies

  • danextian's avatar
    danextian
    Super User
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        danextian
        Super User
        8 years ago
        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.
  • msornakumar's avatar
    msornakumar
    Advocate III
    8 years ago

    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])