Forum Discussion

sguenther's avatar
sguenther
Icon for Advocate II rankAdvocate II
10 years ago
Solved

Ignore Slicer in measure

Hey guys,

 

if I want to ignore a Slicer (on a report page) in a Measure, how would I go about that?

I thought that's when you use the ALL() function like:

 

CALCULATE(
   COUNTROWS(Table1),
   FILTER(ALL(Table1),
      Filter 1 &&
      Filter 2
   )
)

 

But it doesn't seem to work for me. Anything I overlooked?

Thanks!

 

  • sguenther's avatar
    sguenther
    9 years ago

    mnarmeen For my problem the only thing that works is editing the visual intercations. But in your case, since you are using a date filter, I would recommend a different approach. Always create a date/calendar table and don't link this table to the rest of your data model. Then you can always use this table for your filters and have all the freedom when creating measures, like:

     

    Measure =
Calculate(
    Countrows(Table1),
    Filter(Table1,
        Table1[Date] >= MIN(Dateteable[Date]) &&
        Table1[Date] <= MAX(Dateteable[Date]) &&
        other Filter rules....
    )
)

    And if you don't want your date filter to influence your measure, you just leave the first two filter rules out.

     

    Hope this helps. Let me know if I misunderstood the question.

     

    Best,

    Sebastian

14 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    You can edit interactions with slicers and other filtering elements per-visual. Select a visual and open the Format tab in the ribbon. Turn on Edit Interactions and you can turn off interaction with the slicer in question.

     

    There is no way to make a measure ignore a particular slicer. You can make it ignore a column, but it's messy and it will ignore that column everywhere it is referenced, whether it's in a slicer or some other filter. The closest way I know of is to use ALLEXCEPT, which is used to specify every column from that table that you don't want the measure to ignore.

    • jahida's avatar
      jahida
      Icon for Impactful Individual rankImpactful Individual
      10 years ago

      I think the cleanest way to ignore 1 column is ALL('Table'[Column to Ignore]) as one of the filter arguments in the Calculate function. Agreed that Edit Interactions may be preferred in many/most cases.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Thank you so much!
    SOLUTION:
    CALCULATE( COUNTROWS(yourtable), ALL())

    You don't even have to pass any arguments to the ALL() function.
    Nothing will filter, unless you add it to the measure.

  • mnarmeen's avatar
    mnarmeen
    Icon for Advocate I rankAdvocate I
    9 years ago

    Did you manage to solve it? sguenther , I have the same issue where I have a column in table which have to show overall quantity but the date slicer changes it to the current selection. I want to ignore only one column from filter but it doesnot seems to work. Any idea?

    • sguenther's avatar
      sguenther
      Icon for Advocate II rankAdvocate II
      9 years ago

      mnarmeen For my problem the only thing that works is editing the visual intercations. But in your case, since you are using a date filter, I would recommend a different approach. Always create a date/calendar table and don't link this table to the rest of your data model. Then you can always use this table for your filters and have all the freedom when creating measures, like:

       

      Measure =
Calculate(
    Countrows(Table1),
    Filter(Table1,
        Table1[Date] >= MIN(Dateteable[Date]) &&
        Table1[Date] <= MAX(Dateteable[Date]) &&
        other Filter rules....
    )
)

      And if you don't want your date filter to influence your measure, you just leave the first two filter rules out.

       

      Hope this helps. Let me know if I misunderstood the question.

       

      Best,

      Sebastian

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        I was stuck for ages with this, I couldnt think a simple trick like this would do it. Thank you so much