Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi folks,
Can someone please advise how I can synchronize the chart hierarchy selection with a separate slicer?
At this point I have a selector that controls a different aspect of the chart (separate code for the values calculation) and the output must be in sync with the hierachy level. Without this functionality a user has to make appropriate selections in both the chart visual heireachy and in the selector. This is a clumsy ux, to say the least.
I trust that this can be done, but I could not find any proper reference to it anywhere.
I would prefer that my slicer controls the visual hierarcy in the chart, but I would be OK with the visual passing the selected visual level, which then I can use it as a slicer value for my code snippets.
Thank you in advance,
Eugen
Solved! Go to Solution.
Is your Size slicer populated from a small table with just 'Daily', 'Weekly', 'Monthly' in it? If so you might be able to make a bands table that would look something like this.
Size | Band |
Daily | 0-15 |
Daily | 15-30 |
Daily | 30-45 |
Daily | 45-60 |
Daily | 60-75 |
Weekly | 0-50 |
Weekly | 50-100 |
Weekly | 100-150 |
Weekly | 150-200 |
Weekly | 200-250 |
Monthly | 0-500 |
Monthly | 500-1000 |
Monthly | 1000-1500 |
Monthly | 1500-2000 |
Monthly | 2000-2500 |
Then you put the Band from this table in your chart and link it into your model. You could also just use the band from the table above in your slicer and switch your calc based on the selection made, something like this.
Display Amount =
VAR _Size =
SELECTEDVALUE ( 'Bands'[Size] )
RETURN
SWITCH (
_Size,
"Daily", [Daily Measure],
"Weekly", [Weekly Measure],
"Monthly", [Montly Measure]
)
@eugenm yes you can, you can use ISINSCOPE function.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@eugenm yes you can, you can use ISINSCOPE function.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
EUREKA!
ISINSCOPE worked. I've ended up consolidating the Banding Tables into one and created a hierachical Month > Week > Day structure that I then used in a modified switch measure. It works perfectly.
I now deleted my separate band tables and the band slicer - clean and it's working.
Quite a shame that I can't do it via a slicer as I find the hierachical switched at the top of the chart very non user friendly. Not perfect, but perfectly functional.
Thank you again, Parry!
Is your Size slicer populated from a small table with just 'Daily', 'Weekly', 'Monthly' in it? If so you might be able to make a bands table that would look something like this.
Size | Band |
Daily | 0-15 |
Daily | 15-30 |
Daily | 30-45 |
Daily | 45-60 |
Daily | 60-75 |
Weekly | 0-50 |
Weekly | 50-100 |
Weekly | 100-150 |
Weekly | 150-200 |
Weekly | 200-250 |
Monthly | 0-500 |
Monthly | 500-1000 |
Monthly | 1000-1500 |
Monthly | 1500-2000 |
Monthly | 2000-2500 |
Then you put the Band from this table in your chart and link it into your model. You could also just use the band from the table above in your slicer and switch your calc based on the selection made, something like this.
Display Amount =
VAR _Size =
SELECTEDVALUE ( 'Bands'[Size] )
RETURN
SWITCH (
_Size,
"Daily", [Daily Measure],
"Weekly", [Weekly Measure],
"Monthly", [Montly Measure]
)
Accepting this as a solution as well, because the switch that I already had was using such structure.
The banding table had to be different, as the value ranges overlapped.
But using the ISINSCOPE and THIS guide it all worked out well. Done and done! Thanks
The 3 tables are actually banding tables with the additional ranges defined. The ranges are overlapping, which is why I have them separated. See HERE. So, I'm not sure the proposed solution would work. Is there any way to tweak it? I like your idea.
You should be able to do something like this where the index is adjusted so the sort order works but is still unique. Then your banding calcs would just have to also look at the size column and not just the min and max.
Size | Band | Min | Max | Index |
Daily | No Data | -1 | 0 | 1 |
Daily | 0-15 | 0 | 15 | 2 |
Daily | 15-30 | 15 | 30 | 3 |
Daily | 30-45 | 30 | 45 | 4 |
Daily | 45-60 | 45 | 60 | 5 |
Daily | 60-75 | 60 | 75 | 6 |
Daily | 75-90 | 75 | 90 | 7 |
Daily | 90-105 | 90 | 105 | 8 |
Daily | 105-120 | 105 | 120 | 9 |
Daily | 120-135 | 120 | 135 | 10 |
Daily | 135-150 | 135 | 150 | 11 |
Daily | >150 | 150 | 1000 | 12 |
Weekly | No Data | -1 | 0 | 1 |
Weekly | 0-70 | 0 | 70 | 102 |
Weekly | 70-140 | 70 | 140 | 103 |
Weekly | 140-210 | 140 | 210 | 104 |
Weekly | 210-280 | 210 | 280 | 105 |
Weekly | 280-350 | 280 | 350 | 106 |
Weekly | 350-420 | 350 | 420 | 107 |
Weekly | 420-490 | 420 | 490 | 108 |
Weekly | 490-560 | 490 | 560 | 109 |
Weekly | 560-630 | 560 | 630 | 110 |
Weekly | 630-700 | 630 | 700 | 111 |
Weekly | >700 | 700 | 70000 | 112 |
Monthly | No Data | -1 | 0 | 1 |
Monthly | 0-0.3 | 0 | 300 | 1002 |
Monthly | 0.3-0.6 | 300 | 600 | 1003 |
Monthly | 0.6-0.9 | 600 | 900 | 1004 |
Monthly | 0.9-1.2 | 900 | 1200 | 1005 |
Monthly | 1.2-1.5 | 1200 | 1500 | 1006 |
Monthly | 1.5-1.8 | 1500 | 1800 | 1007 |
Monthly | 1.8-2.1 | 1800 | 2100 | 1008 |
Monthly | 2.1-2.4 | 2100 | 2400 | 1009 |
Monthly | 2.4-2.7 | 2400 | 2700 | 1010 |
Monthly | 2.7-3 | 2700 | 3000 | 1011 |
Monthly | >3 | 3000 | 10000000 | 1012 |
I basically had to create set by having 3 sets of columns for Daily, Weekly and Monthly (Band_Day, Min_Day, Max_Day and same for weekly/monthly). Just in case someone will find this useful.
Cheers
@eugenm I don't think it is possible but there are workarounds for dynamic x-axis which can change based on your selection. You can also look at my post on mixing granularities and tweak it as per your need but there is no way to auto drill up/down based on the selection.
Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
I certainly look at your blog post, thanks.
If using the filter to control the hierarchy display level in the chart is not an option, is there any option to pass the hierarchy level from the chart - same as you would from a slicer?
@eugenm not sure I followed your question, can you share some screenshots and explain?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi Perry,
Please see the details HERE
I basically have 3 levels of banding with a slicer that dictates which banding calculation is used (band size calculation). I would like for it to also switch the chart's hierachical level to the respective bands, which selects the bands displayed. Without such sync I get no data or weird out of sync charts.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
76 | |
74 | |
56 | |
45 |
User | Count |
---|---|
117 | |
105 | |
77 | |
66 | |
64 |