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

123abc's avatar
123abc
Community Champion
2 years ago

Got it! Given the structure of your dataset, where each manager level is represented as a separate column, you'll need to adjust the DAX logic to handle this specific structure. Here's a revised approach:

  1. Setup Hierarchy Slicer:

    • Drag all the "Level X Manager" columns into a single slicer. This allows users to select a manager regardless of which level they're on.

  2. Bar Chart Setup:

    • Create a bar chart visual.

  3. DAX Expression for Dynamic Y-axis:

Given your dataset structure, let's create a DAX measure to dynamically select the managers for the bar chart based on the slicer selection:

 

DynamicYAxis =
VAR SelectedManager = SELECTEDVALUE('YourTable'[SelectedManagerColumnName]) // Adjust this based on your slicer column name
VAR SelectedManagerIndex =
SWITCH(
TRUE(),
SelectedManager = 'YourTable'[Level 1 Manager], 1,
SelectedManager = 'YourTable'[Level 2 Manager], 2,
SelectedManager = 'YourTable'[Level 3 Manager], 3,
SelectedManager = 'YourTable'[Level 4 Manager], 4,
SelectedManager = 'YourTable'[Level 5 Manager], 5,
BLANK() // Default if none match
)

RETURN
IF(
ISBLANK(SelectedManager),
BLANK(),
CALCULATE(
COUNTROWS('YourTable'),
FILTER(
'YourTable',
'YourTable'[Level 1 Manager] = SelectedManager ||
'YourTable'[Level 2 Manager] = SelectedManager ||
'YourTable'[Level 3 Manager] = SelectedManager ||
'YourTable'[Level 4 Manager] = SelectedManager ||
'YourTable'[Level 5 Manager] = SelectedManager
)
)
)

 

  • Replace 'YourTable', SelectedManagerColumnName, and the other column names (Level 1 Manager, Level 2 Manager, etc.) with your actual table and column names.

  1. Assign DAX Measure to Bar Chart:

    • Drag the DynamicYAxis DAX measure to the Values section of the bar chart.

  2. Interactions:

    • Ensure that there are no undesired interactions between the slicer and the bar chart that could affect the dynamic behavior.

  3. Testing:

    • Select different managers from the slicer to verify if the bar chart updates correctly to display managers under the selected manager.

This approach should help you achieve the desired functionality based on your dataset structure.

 

 

HenSza's avatar
HenSza
Frequent Visitor
2 years ago

Thank you, again. I try to understand the formaula of DynamicYAxis. "SelectedManagerColumnName" must be a column, and you wrote "Replace with actual column name" But I have multiple manager coulmns, what should be this one?

What is the purpose of SelectedManagerIndex?