Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
HenSza
Frequent Visitor

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 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?

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

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.

vyangliumsft_0-1703039610924.png

vyangliumsft_1-1703039610925.png

2. Add Column – Index Column – From 1.

vyangliumsft_2-1703039648268.png

3. Create calculated column.

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

vyangliumsft_3-1703039662828.png

4. Create calculated table.

Use the columns of the new table as slicers

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

vyangliumsft_4-1703039662829.png

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.

vyangliumsft_5-1703039702800.png

7. Result:

vyangliumsft_6-1703039702803.png

 

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

View solution in original post

6 REPLIES 6

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
Community Support
Community Support

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.

vyangliumsft_0-1703039610924.png

vyangliumsft_1-1703039610925.png

2. Add Column – Index Column – From 1.

vyangliumsft_2-1703039648268.png

3. Create calculated column.

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

vyangliumsft_3-1703039662828.png

4. Create calculated table.

Use the columns of the new table as slicers

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

vyangliumsft_4-1703039662829.png

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.

vyangliumsft_5-1703039702800.png

7. Result:

vyangliumsft_6-1703039702803.png

 

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
Community Champion
Community Champion

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
Frequent Visitor

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
Community Champion
Community Champion

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
Frequent Visitor

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?

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Kudoed Authors