Creating a text parameter to dynamically change the X axis of visuals

[Tae]

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

[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,

[Tae]

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]

Hi @Tae ,

Please try to create a calculated table instead of a measure.

Best regards,

[Tae]

Thanks Dong.

This is the table I get.

I don't know why but the lower level hierarchies are consolidated into the highest level - BUSINESS GROUP.

Axis = UNION (

CROSSJOIN ( { "Level 1" }, DISTINCT ( dashboard[BUSINESS_GROUP] ) ),

CROSSJOIN ( { "Level 2" }, DISTINCT ( dashboard[DIVISION]) ),

CROSSJOIN ( { "Level 3" }, DISTINCT ( dashboard[DEPARTMENT_REGION]) )

)

[v-lid-msft]

Hi @Tae ,

Sorry for forgetting to mention that we change the column name in our workaround. the BUSINESS_GROUP is just a auto-generate column name.

Best regards,

[v-lid-msft]

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,