Filter results from a slicer getting results on two columns

[Sesese]

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

[v-rongtiep-msft]

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

[Sesese]

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

[v-rongtiep-msft · Accepted 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

[Sesese]

Many thanks, it works perfectly.

Just a quick question, would be possibile to make it without utting the first 3 column inside the table?

[v-rongtiep-msft]

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

[Sesese]

Hi @v-rongtiep-msft 

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

[FreemanZ]

what do you expect on the second slicer?

[Sesese]

Hi @FreemanZ,

i would say the filtered list from the first slicer as described in the first message

[FreemanZ]

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?

[Sesese]

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

[FreemanZ]

could you depict your expected result with a table?