Forum Discussion

Tae's avatar
Tae
Frequent Visitor
6 years ago

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 t...
Need Help
Tips and Tricks
v-lid-msft's avatar
v-lid-msft
Icon for Community Support rankCommunity Support
6 years ago

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,