Dynamic column for visual

[HenSza]

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 select a manager on Level 3, I want to seeon the visual  the managers on Level 4 who report to selected Manager.) How can I do that?

[siddhartha0208]

I need your help today. I've a table with three Columns: 
tablename: T,
Columns: F1, F2, F3
On a slicer F1 is selecting Values
I want to draw a clustered bar graph such that the Y Axis of the bar chart is governed by the selection.I'm trying to add a dynamic column in the same table:
DynamicAxis = IF(SELECTEDVALUE('T'[F1]) = "X",'T'[F2],'T'[F3])
This one is not working when X is selected... Any guidance would be super helpful.

[v-yangliu-msft · Accepted solution]

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]

To achieve the dynamic column behavior you're looking for in your Power BI visual, you can use DAX (Data Analysis Expressions) to create calculated columns and measures. Here's a step-by-step guide on how you can set this up:

Assuming you have a table named "Managers" with columns like "ManagerID," "ManagerName," and "ManagerLevel," you can follow these steps:

1. Create a Hierarchy Table:

   • Create a new table that represents the hierarchy of managers. This table should have columns like "ManagerID," "ManagerName," "ManagerLevel," and a column linking each manager to their parent manager, for example, "ParentManagerID."

2. Create Relationships:

   • Establish relationships between the "Managers" table and the hierarchy table based on the "ManagerID" and "ParentManagerID" columns.

3. Create Calculated Column:

   • In the "Managers" table, create a calculated column that identifies the selected manager's level. Let's call this column "SelectedManagerLevel."

Dax Measure:

SelectedManagerLevel =
IF (
Managers[ManagerID] = SELECTEDVALUE( 'HierarchySlicer'[ManagerID] ),
Managers[ManagerLevel],
BLANK()
)

Create Measures:

• Create a measure that filters the managers based on the selected manager's level. Let's call this measure "FilteredManagers."

Dax Measure:

FilteredManagers =
CALCULATETABLE (
VALUES ( Managers[ManagerID] ),
Managers[ManagerLevel] = [SelectedManagerLevel]
)

Create another measure that counts the number of managers at the next level. Let's call this measure "NextLevelManagersCount."

Dax Measure:

NextLevelManagersCount =
COUNTROWS (
FILTER (
VALUES ( Managers[ManagerID] ),
Managers[ParentManagerID] IN VALUES ( 'FilteredManagers'[ManagerID] )
)
)

1. Configure Bar Chart Visual:

   • Use the "FilteredManagers" measure for the Y-axis in your bar chart.
   • Optionally, use the "NextLevelManagersCount" measure to determine the number of managers at the next level for the X-axis.

Now, when you select a manager in the hierarchy slicer, the bar chart should dynamically show the managers at the next level who report to the selected manager. Adjust the DAX expressions based on your data model and requirements.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[HenSza]

I highly appreciate your detailed guide.

Dataset looks different.

Manager Levels are in different columns since the table contains the data of employees. Columns are:

User id | Level 1 Manager | Level 2 Manager | Level 3 Manager | Level 4 Manager | Level 5 Manager

I put columns Level 1-5 Manager into the slicer. In the visual I want show managers under the selected manager.

[123abc]

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]

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?