Forum Discussion
Helper IISynchronize chart hierarchy level selector with separate slicer
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] )
Helper IIThe 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.
Super UserYou 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
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