Synchronize chart hierarchy level selector with separate slicer

[eugenm]

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

[jdbuchanan71 · Accepted solution]

@eugenm 

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]
    )

[parry2k · Accepted solution]

@eugenm yes you can, you can use ISINSCOPE function.

[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!

[jdbuchanan71 · Accepted solution]

@eugenm 

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]

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

[eugenm]

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.

[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]

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

[parry2k]

@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? 

[parry2k]

@eugenm not sure I followed your question, can you share some screenshots and explain?

[eugenm]

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.