Forum Discussion

doppietta's avatar
doppietta
Frequent Visitor
8 years ago

selective slicer

Hi all,   I need to plot on clustered column chart the range distribution twice: the first one is the unfiltered version (made of by all the records in the table) and I need the second one to be dy...
Need Help
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi,

 

You will need to create a new custom measure that ignores any filters set on the page. This can be done using the CALCULATE() function. Your aqua series can be linked to your core data, and your charcoal series can be linked to the new measure. This will mean that whenever you apply filters, the aqua series will be filtered but the charcoal series will stay as it is.

 

If you can post an example of your data structure, I (or another helpful member) can give you an example CALCULATE() function to use.

doppietta's avatar
doppietta
Frequent Visitor
8 years ago

Hi Tobias,

 

it could be very useful for me because I'm at the very beginning with powerbi.

This is my data model:

 

DAYTIME is the field used in the slicer

RHGL_CUSTOM_RANGE2 is used to build tailored bins

Period is the legend

D_RHGL is the value to be aggregated (count) over the ranges.

 

Thank you,

Alberto

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi,

     

    Try this: NOTE - 'Table' is the name of your data table. Replace the word 'Table' in the formula below with your own table name (remember to use the quotation marks).

     

    Custom Measure = CALCULATE(COUNT('Table'[D_RHGL]),ALL('Table'))

     

    Then bind this to the charcoal series in your chart.

    • doppietta's avatar
      doppietta
      Frequent Visitor
      8 years ago

      Hi Tobias,

       

      I had to change something in the formula but it seems to work now. The measure I used is:

       

      MyMeasure = CALCULATE(COUNT(HLCO_FREQ_DISTRIBUTION_1[D_RGHL]),ALLEXCEPT(HLCO_FREQ_DISTRIBUTION_1, HLCO_FREQ_DISTRIBUTION_1[RGHL_CUSTOM_RANGE2]))

       

      Unfortunately I have another issue: if narrowing down the span of the aqua series I hit a period for whom that series has no values in a range, that range isn't showed (even if the other series has values in that range).

      Maybe it should be more clear in the following images:

       

      In this image all the ranges I defined are visible because both series have at least one element in each range.

       

      In this image I narrowed down the slicer and the latest range (>200) disappeared even if the charcoal series has values in that range.

       

      In any case thank you very much Tobias.

      Regards,

      Alberto

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        In the chart customisation pane, if you right-click the field that is used by the aqua series, you should see an option 'Show items with no data'.

         

        Ticking this option will always show all of your category buckets, even when there are no values in the filtered view.

         

        (P.S. Sorry I missed the fact that needed a 'Count' rather than a 'Sum' from your original post).