Forum Discussion
Helper IISynchronize chart hierarchy level selector with separate slicer
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
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] )
11 Replies
eugenmHi 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.
- parry2k
Super User
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.⚡
- eugenm
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?
- jdbuchanan71
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] )- jdbuchanan71
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
- eugenm
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!