Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 ann...
  • PaulDBrown's avatar
    PaulDBrown
    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.