Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
Hi @Anonymous
There are two approaches I can think of:
ManagersTable = VAR M1 = SELECTCOLUMNS ( Table, "Manager", Table[Manager1] ) VAR M2 = SELECTCOLUMNS ( Table, "Manager", Table[Manager2] ) RETURN UNION ( M1, M2 )
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
)
Proud to be a Super User!
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.
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])
Hi @Anonymous
There are two approaches I can think of:
ManagersTable = VAR M1 = SELECTCOLUMNS ( Table, "Manager", Table[Manager1] ) VAR M2 = SELECTCOLUMNS ( Table, "Manager", Table[Manager2] ) RETURN UNION ( M1, M2 )
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
)
Proud to be a Super User!
@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?
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |