Forum Discussion

HenSza's avatar
HenSza
Frequent Visitor
2 years ago
Solved

Dynamic column for visual

I have a hierarchy slicer with various levels of Managers (Level1, Level2, Level3 etc.) and also have a bar chart visual. I want to show the on Y axis the Managers under the selected Manager. (e.g I ...
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi HenSza ,

     

    Here are the steps you can follow：

    1. In Power Query -- Select [Level 1 Manager], [Level 2 Manager], [Level 3 Manager], [Level 4 Manager] – Transform – Unpivot Columns.

    2. Add Column – Index Column – From 1.

    3. Create calculated column.

    Rank =
RANKX(
    FILTER(ALL('Table'),'Table'[User id]=EARLIER('Table'[User id])),[Index],,ASC)

    4. Create calculated table.

    Use the columns of the new table as slicers

    Table 2 =
DISTINCT('Table'[Attribute])

    5. Create measure.

    Flag =
var _select=SELECTEDVALUE('Table 2'[Attribute])
var _level = MAXX(FILTER(ALL('Table'),'Table'[Attribute]=_select),[Rank])
return
IF(
    MAX('Table'[Rank])>_level,1,0)

    6. Place [Flag]in Filters, set is=1, apply filter.

    7. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly