Forum Discussion
Slice on column header
Hi,
I'm looking to slice on column header, I had a look around the forums and I'm not sure if this is possible. I'll outline what I want to do:
-Create a python histogram with some custom annotations on it
-Have 5-10 columns all dragged onto the same visual
-Only select which columns of data to display based on their name and no other criteria
This would allow me to have one graph and display multiple lines on the one graph, as opposed to having many small graphs to show the same thing. Is this a simple thing to do?
Thanks
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.
5 Replies
- amitchandakSuper User
I did not get it completely. But check
- AnonymousNot applicable
Hi, not sure I totally follow your link. I'll try and explain a bit better with a graph. What I would like is a line chart say with 5 lines on it, each one is a different series. I'd like the user of the graph to be able to select what series to display on the graph at any one time, based on the name of the series or measure. Does that make sense?
- PaulDBrownCommunity Champion
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.