Forum Discussion
Creating a text parameter to dynamically change the X axis of visuals
Hi,
I'm thinking of creating a workforce snapshot dashboard which has the ability to drill down into the lowest level management/business unit.
I understand that the hierarchy slicer allows me to do so, but I also want the visuals linked to this slicer to display the list of business units at the selected hierarchy level.
Group
Division1
Department1
Team1
Team2
Department2
Team3
Team4
Team5
If I have a hierarchy like the above, is there a way I can make my visuals to dynamically display list of BUs according to the the selected hierarchy level (Group being level 1 and Team being level 4) by creating a parameter?
(i.e. when level 3 is selected, the visuals should display roll up figures of all BUs in level 3 - Department 1 & 2 )
Tableau parameter allows users to create text parameters, but it seems only numeric parameter is allowed in Power BI.
So I assume I need to write a DAX statement to workaround this, but I'm having difficulty figuring out a solution for this on my own.
Any help will be greatly appreciated.
Regards,
Tae
6 Replies
v-lid-msftCommunity Support
Hi Tae ,
We can use the following steps as a workaround to meet your requirement:
1. create a axis table
Axis = UNION ( CROSSJOIN ( { "Level 1" }, DISTINCT ( 'Table'[Group] ) ), CROSSJOIN ( { "Level 2" }, DISTINCT ( 'Table'[Division] ) ), CROSSJOIN ( { "Level 3" }, DISTINCT ( 'Table'[Department] ) ), CROSSJOIN ( { "Level 4" }, DISTINCT ( 'Table'[Team] ) ) )2. create a measure used in the visual
ValueSum = SWITCH ( SELECTEDVALUE ( 'Axis'[Axis Dimension] ), "Level 1", CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Group] IN FILTERS ( 'Axis'[Axis Value] ) ), "Level 2", CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Division] IN FILTERS ( 'Axis'[Axis Value] ) ), "Level 3", CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Department] IN FILTERS ( 'Axis'[Axis Value] ) ), "Level 4", CALCULATE ( SUM ( 'Table'[Value] ), 'Table'[Team] IN FILTERS ( 'Axis'[Axis Value] ) ) )Please also refer to the similar thread: https://community.powerbi.com/t5/Desktop/Dynamic-change-in-X-Axis/m-p/86167
Best regards,- v-lid-msft
Hi Tae ,
How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?
Best regards,Hi Dong,
When I try to create the axis table, I get an warning message saying "the expression refers to multiple columns. Multipler columns cannot be conversted to a scalar value".
Regards,
Tae
- v-lid-msft
