Dynamic change of X axis based on slicer selected

[mahesh_marathe]

Hello Friends,

I need to show the list of employees coming under the manager when I select him in slicer, here is the scenario, I have employees with 6 levels. On my report I have added 6 slicers, If I select L6 slicer it should show only L6 level employees that I selected in slicer bcoz that is the lowest level, when I select any manager from L5 it should show me the L6 employee under that L5 manager.

when I select L4 employee it should show me employee under him from L5 level, so If both L4 and L5 is selected then I should get only L6 employees under L5, If I select L3 and L4 in slicer I should see L5 employees under L4 and so on. So basically my x asis column depends on slicer level.

I have created below columns in my table

L2 = CALCULATE(VALUES(DimPerson[Name]), TOPN(1, FILTER(DimPerson, DimPerson[Alias] = Survey[M2Alias]), DimPerson[Name]))

L3 = CALCULATE(VALUES(DimPerson[Name]), TOPN(1, FILTER(DimPerson, DimPerson[Alias] = Survey[M3Alias]), DimPerson[Name])) ............

L6 = CALCULATE(VALUES(DimPerson[Name]), TOPN(1, FILTER(DimPerson, DimPerson[Alias] = Survey[M6Alias]), DimPerson[Name]))

I can get L6 level employyes under L5 using this DAX expression >>FIRSTNONBLANK(Survey[L6],Survey[L5])

Now my question is how do check which slicer value has selected so that I can apply if condition and based on that level I will show >> FIRSTNONBLANK(Survey[L6],Survey[L5]) or FIRSTNONBLANK(Survey[L5],Survey[L4]) or FIRSTNONBLANK(Survey[L4],Survey[L3])

Basically I need to use this FIRSTNONBLANK(Survey[L6],Survey[L5]) expression based on the lowest level of slicer selected.

Please suggest the approach

Thanks,

[Eric_Zhang]

---

@mahesh_marathe wrote:

Hello Friends,

I need to show the list of employees coming under the manager when I select him in slicer, here is the scenario, I have employees with 6 levels. On my report I have added 6 slicers, If I select L6 slicer it should show only L6 level employees that I selected in slicer bcoz that is the lowest level, when I select any manager from L5 it should show me the L6 employee under that L5 manager.

when I select L4 employee it should show me employee under him from L5 level, so If both L4 and L5 is selected then I should get only L6 employees under L5, If I select L3 and L4 in slicer I should see L5 employees under L4 and so on. So basically my x asis column depends on slicer level.

I have created below columns in my table

L2 = CALCULATE(VALUES(DimPerson[Name]), TOPN(1, FILTER(DimPerson, DimPerson[Alias] = Survey[M2Alias]), DimPerson[Name]))

L3 = CALCULATE(VALUES(DimPerson[Name]), TOPN(1, FILTER(DimPerson, DimPerson[Alias] = Survey[M3Alias]), DimPerson[Name])) ............

L6 = CALCULATE(VALUES(DimPerson[Name]), TOPN(1, FILTER(DimPerson, DimPerson[Alias] = Survey[M6Alias]), DimPerson[Name]))

I can get L6 level employyes under L5 using this DAX expression >>FIRSTNONBLANK(Survey[L6],Survey[L5])

Now my question is how do check which slicer value has selected so that I can apply if condition and based on that level I will show >> FIRSTNONBLANK(Survey[L6],Survey[L5]) or FIRSTNONBLANK(Survey[L5],Survey[L4]) or FIRSTNONBLANK(Survey[L4],Survey[L3])

Basically I need to use this FIRSTNONBLANK(Survey[L6],Survey[L5]) expression based on the lowest level of slicer selected.

Please suggest the approach

Thanks,

---

@mahesh_marathe

How is your table like? Would you mind post any sample data?

By the way, For this kind of question, I think it would be more flexible if you could re-mode your data like below. Use the MAX(TABLE[No]) to get the selected Level and use Filter like Filter(Table, [No]=MAX(TABLE[No]) -1 ) to get the direct subordinates.