Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all
I have a load of bar/column chart visuals that show information split by the 5 levels of the hierachy that I have to report on (from top to bottom) - Division, Area, Service, Team and Worker. Using the drillup and drilldown arrows allows people to view the information at the level that they are interested in.
What I would like is for them to initally select from a slicer the Division, Area, Service or Team that they are interested in and for all the graphs to then automatically show the next level of information down. So if the person selects a Service, I would like the graphs to automatically show the data at Team Level (for the service they have selected). If they select Team it would show Worker level etc.
I can not think of a way to achieve this but hoping that somone might have a clever idea as to how it can done.
Any help greatly appreciated
Thanks
Chris
Solved! Go to Solution.
Hi @nunnc01 ,
This can be achieved using two disconnected tables and a measure with a switch formula:
Slicer =
UNION (
ADDCOLUMNS (
VALUES ( 'Table'[Area] ),
"Type", "Area",
"Group Level", "Division"
),
ADDCOLUMNS (
VALUES ( 'Table'[Dvision] ),
"Type", "Division",
"Group Level", "Service"
),
ADDCOLUMNS (
VALUES ( 'Table'[Service] ),
"Type", "Service",
"Group Level", "Team"
),
ADDCOLUMNS (
VALUES ( 'Table'[Team] ),
"Type", "Team",
"Group Level", "NODETAIL"
)
)
X Axis Values =
UNION (
ADDCOLUMNS (
VALUES ( 'Table'[Area] ),
"Type", "Area",
"Group Level", "Division"
),
ADDCOLUMNS (
VALUES ( 'Table'[Dvision] ),
"Type", "Division",
"Group Level", "Service"
),
ADDCOLUMNS (
VALUES ( 'Table'[Service] ),
"Type", "Service",
"Group Level", "Team"
),
ADDCOLUMNS (
VALUES ( 'Table'[Team] ),
"Type", "Team",
"Group Level", "NODETAIL"
)
)
Values By next level =
SWITCH (
SELECTEDVALUE ( Slicer[Type] ),
"Division",
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALL ( 'Table'[Area], 'Table'[Dvision] ),
'Table'[Area]
IN VALUES ( 'X-Axis Values'[Area] )
&& 'Table'[Dvision] IN VALUES ( Slicer[Area] )
)
),
"Area",
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALL ( 'Table'[Service], 'Table'[Area] ),
'Table'[Service]
IN VALUES ( 'X-Axis Values'[Area] )
&& 'Table'[Area] IN VALUES ( Slicer[Area] )
)
),
"Service",
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALL ( 'Table'[Team], 'Table'[Service] ),
'Table'[Team]
IN VALUES ( 'X-Axis Values'[Area] )
&& 'Table'[Service] IN VALUES ( Slicer[Area] )
)
)
)
I finish the setup at Team level but you can setup this to multiple leves just need to add it to the measure.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @nunnc01 ,
This can be achieved using two disconnected tables and a measure with a switch formula:
Slicer =
UNION (
ADDCOLUMNS (
VALUES ( 'Table'[Area] ),
"Type", "Area",
"Group Level", "Division"
),
ADDCOLUMNS (
VALUES ( 'Table'[Dvision] ),
"Type", "Division",
"Group Level", "Service"
),
ADDCOLUMNS (
VALUES ( 'Table'[Service] ),
"Type", "Service",
"Group Level", "Team"
),
ADDCOLUMNS (
VALUES ( 'Table'[Team] ),
"Type", "Team",
"Group Level", "NODETAIL"
)
)
X Axis Values =
UNION (
ADDCOLUMNS (
VALUES ( 'Table'[Area] ),
"Type", "Area",
"Group Level", "Division"
),
ADDCOLUMNS (
VALUES ( 'Table'[Dvision] ),
"Type", "Division",
"Group Level", "Service"
),
ADDCOLUMNS (
VALUES ( 'Table'[Service] ),
"Type", "Service",
"Group Level", "Team"
),
ADDCOLUMNS (
VALUES ( 'Table'[Team] ),
"Type", "Team",
"Group Level", "NODETAIL"
)
)
Values By next level =
SWITCH (
SELECTEDVALUE ( Slicer[Type] ),
"Division",
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALL ( 'Table'[Area], 'Table'[Dvision] ),
'Table'[Area]
IN VALUES ( 'X-Axis Values'[Area] )
&& 'Table'[Dvision] IN VALUES ( Slicer[Area] )
)
),
"Area",
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALL ( 'Table'[Service], 'Table'[Area] ),
'Table'[Service]
IN VALUES ( 'X-Axis Values'[Area] )
&& 'Table'[Area] IN VALUES ( Slicer[Area] )
)
),
"Service",
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALL ( 'Table'[Team], 'Table'[Service] ),
'Table'[Team]
IN VALUES ( 'X-Axis Values'[Area] )
&& 'Table'[Service] IN VALUES ( Slicer[Area] )
)
)
)
I finish the setup at Team level but you can setup this to multiple leves just need to add it to the measure.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
99 | |
80 | |
77 | |
66 |
User | Count |
---|---|
134 | |
108 | |
104 | |
83 | |
73 |