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.
Hey guys,
kinda new here working on PowerBi and I need your help.
In my table I have three columns: Manager Level 1, Manager Level 2 and Staff and every column represent one order completed by Staff user as follows
ID | Manager LV 1 | Manager LV 2 | Staff |
1 | John White | Red Snox | The Rock |
2 | John White | Willie Wonka | Aydan |
3 | John White | John White | John White |
4 | John White | Willie Wonka | Ben Stiller |
5 | John Cena | John Cena | John Cena |
6 | John Cena | Lionel Messi | Paulo Dybala |
7 | John Cena | Maradona | Pelè |
8 | John Cena | John Cena | Robert Lewandoski |
9 | John Cena | John Cena | John Cena |
What I need is to create two slicers, one with the list of Managers LV.1 that filter the above excel table showing results from both Manager LV 2 & Staff; for example selecting John Cena I would like to see data from John Cena, Lionel Messi, Maradona & Robert Lewandoski.
The list of Manager lv 1 is easy with the Distinct formula, but then I don't know how to manage the last part.
Thanks guys
Solved! Go to Solution.
Hi @Sesese ,
Sorry I was late. Please refer to my pbix file to see if it helps you.
Create a table first.
manager = SUMMARIZE('Table','Table'[Manager LV 1])
Then put it into the slicer.
Then create a measure.
Measure = var _1=SELECTEDVALUE(manager[Manager LV 1])
var _1re=IF(_1<>MAX('Table'[Manager LV 2]),MAX('Table'[Manager LV 2]),MAX('Table'[Staff]))
return
IF(_1=SELECTEDVALUE('Table'[Manager LV 1]),_1re,BLANK())
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @Sesese ,
When you select John Cena, Is the following table what you would like to see?
Why the 6th and 7th rows show manager lv 2 name? The rows 5 8 and 9 are the staff names? Filter based on what?
How about when you select John White?
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi ,
first of all thank you for your answer.
I made the example with John Cena, clicking on his name I would like to see the result on a unique column showing the 5 names. As well with John White, I need to see as result Red Sox, Willie Wonka and John White.
Considering the staff level as N-2 level, Manager lv. 1 as N-1 and Manager lv. 2 as N, I would like to see the -1 level name of people reporting to the manager lv 2 manager. If John Cena is only written in Manager lv. 2 I would like to see the name in manager lv.1, if is written also in manager lv.1 I would like to see the staff level name otherwise if written in all three columns, I would like to see his name.
Many thanks
Hi @Sesese ,
Sorry I was late. Please refer to my pbix file to see if it helps you.
Create a table first.
manager = SUMMARIZE('Table','Table'[Manager LV 1])
Then put it into the slicer.
Then create a measure.
Measure = var _1=SELECTEDVALUE(manager[Manager LV 1])
var _1re=IF(_1<>MAX('Table'[Manager LV 2]),MAX('Table'[Manager LV 2]),MAX('Table'[Staff]))
return
IF(_1=SELECTEDVALUE('Table'[Manager LV 1]),_1re,BLANK())
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Many thanks, it works perfectly.
Just a quick question, would be possibile to make it without utting the first 3 column inside the table?
Hi @Sesese ,
We need the three columns to get the result.
Measure = var _1=SELECTEDVALUE(manager[Manager LV 1])
var _1re=IF(_1<>MAX('Table'[Manager LV 2]),MAX('Table'[Manager LV 2]),MAX('Table'[Staff]))
return
IF(_1=SELECTEDVALUE('Table'[Manager LV 1]),_1re,BLANK())
The measure can help you.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
thank you for this answer, I've solved everything with this formula
Just the last quick question, how can I have the name from the second measure formula only once without repeating them?
Many thanks
what do you expect on the second slicer?
aha, then
1) create a table visual with the Manager LV 2 column, (do not aggregate)
2) make a selection on the LV1 slicer, the table visual shall reflect your selection accordingly.
Is it what you expect?
Partially cause some data must come from manager lv2 but others from Staff.
considering the table above, i expect to see manager lv 2 name in rows 6 and 7, but in rows 5 8 and 9 I expect the staff name
could you depict your expected result with a table?
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 |
---|---|
110 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |