Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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
Hi @Tae ,
Please try to create a calculated table instead of a measure.
Best regards,
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.
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,
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,
User | Count |
---|---|
92 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |