cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mrainey Member
Member

Tricky Slicer Situation

I have a table that looks something like this:

1.PNG

 

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:

1.PNG

 

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?

1 ACCEPTED SOLUTION

Accepted Solutions
danextian New Contributor
New Contributor

Re: Tricky Slicer Situation

Hi @mrainey

 

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
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
4 REPLIES 4
danextian New Contributor
New Contributor

Re: Tricky Slicer Situation

Hi @mrainey

 

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
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
msornakumar Regular Visitor
Regular Visitor

Re: Tricky Slicer Situation

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.pngRelationship

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

 

Output.png 

mrainey Member
Member

Re: Tricky Slicer Situation

@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 New Contributor
New Contributor

Re: Tricky Slicer Situation

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.
"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 28 members 910 guests
Please welcome our newest community members: