Forum Discussion
Slice on column header
- 6 years ago
Anonymous
You can set up a visual to display values based on a slicer selection (with a little bit of work...)
You will need to create a table (Enter Data in the ribbon under the Home tab) and type in a column the measure names for the slicer). This table must not have any relationhips with other tables.
In my example, this is the table:
Next you create a new measure for each of the measures you wish to include in the visual:
As an example:
Sales 2018 (for viz) = VAR calc = CALCULATE(DISTINCTCOUNT('Select Measure'[Selected Measure]); FILTER('Select Measure'; 'Select Measure'[Selected Measure]= "Sales 2018")) RETURN IF(calc= 1; [Sales 2018]; BLANK())In effect, the VAR returns 1 if the value "Sales 2018" in my measure selection table is selected. The measure then computes the [Sales 2018] measure (which I have created previously) if the corresponding selection is made in the measure slicer. If it isn't selected, it returns BLANK (), which means no value will be plotted in the visual.
You need to create an equivalent measure for each of the measures in your measure selection slicer.
And you get this result:
The only thing to be aware of is that if there is no selection in the measure slicer, the measures render true (1) and therefore all the values are displayed in the visual.
If you prefer to display values ONLY if there is a selection in the slicer, you can create the following:
Countrows Select Measure = IF(ISFILTERED('Select Measure'[Selected Measure]); COUNTROWS(VALUES('Select Measure'[Selected Measure])); BLANK())You can see how the result in the following comparison (the cards in the middle of the images are a simple COUNTROWS; you can see that if there is no selection in the slicer, the values computed are 4 (the same as we get if we select all values. With the [Countrows Select Measure] it only returns a vlue if there is a selection.
And include this in your new measures to look like this:
Sales 2018 (sel) = VAR calc = CALCULATE(DISTINCTCOUNT('Select Measure'[Selected Measure]); FILTER('Select Measure'; 'Select Measure'[Selected Measure]= "Sales 2018")) RETURN IF(ISBLANK([Countrows Select Measure]); BLANK() ; IF(calc= 1; [Sales 2018]; BLANK()))If you use these latter measures, the chart will be blank unless there is at least one selection in the slicer:
As opposed to the messy alternative (even worse if you have 5 measures) of:
Hope this helps. Do let us know if there is something you need clarifying.
Hello!
This was exactly what I was looking for, really appreciate the thoughtful answer you gave to Ronan. I have a small question though, in this example, what does the if loop do? Should I replace it with the first value of my Select Measure table?
Sales 2018 (for viz) =
VAR calc = CALCULATE(DISTINCTCOUNT('Select Measure'[Selected Measure]);
FILTER('Select Measure';
'Select Measure'[Selected Measure]= "Sales 2018"))
RETURN
IF(calc= 1; [Sales 2018]; BLANK())
Thanks in advance!
This is one of the measures you use in the visual (you need one for each metric you wish to display). The IF expression is there to reveal the measures' values if the measure is selected in the slicer (if not it remains blank)