Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Make measure ignore specific filter

This should be simple but I just can’t solve it :) I have a report page with several filters and a measure Amounts = SUM(Table.Amount). Now I’d like Amounts to ignore just one of the filters, a Page...
Need Help
  • AkhilAshok's avatar
    AkhilAshok
    7 years ago

    This is not really an issue. It is clearly explained in this article from Alberto. It is happening because of Auto Exist feature in DAX.

    Besides, creating a seperate dimension table is not really a workaround, but rather a best practice. A Star Schema is always considered best when working with Power BI Datamodel. This is what makes it more powerful!

     

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hey guys!

It's dead simple, really. Use the ALL function to remove a filter on a specific column like this:

Measure = calculate(Expression,ALL(column-you-want-to-remove-filter))

That's it. ALL, used inside a CALCULATE expression, works like REMOVEFILTER.

  • akshaybanaye's avatar
    akshaybanaye
    Regular Visitor
    2 years ago

    What if the filter is also using a measure (visual level filter)?

     

  • shas's avatar
    shas
    Frequent Visitor
    2 years ago

    this does not work anymore

  • wagrezy's avatar
    wagrezy
    Icon for Helper I rankHelper I
    7 years ago

    Yes, confirmed. It works with Calculate([expression],ALL([column name])).

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      I see a lot of responses that it works with ALL, but no one adresses the specifik problem/bug in my example (download file below to see my example). There I illustrate that in some cases it won't work.

      Download file

      • AkhilAshok's avatar
        AkhilAshok
        Icon for Solution Sage rankSolution Sage
        7 years ago

        This is not really an issue. It is clearly explained in this article from Alberto. It is happening because of Auto Exist feature in DAX.

        Besides, creating a seperate dimension table is not really a workaround, but rather a best practice. A Star Schema is always considered best when working with Power BI Datamodel. This is what makes it more powerful!

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Greg_Deckler 

    Hi Greg,

     

    My measure is giving wrong column sub total but correct row sub total.

     

    My Actual Measure in the report:

     

    Measure =

    VAR Y=
    CALCULATE(SUMX(VALUES('CurrentTimeEntry Details'),[ActualHours]),ALLSELECTED('Staff Details'[Employee Name])]))

    VAR X = CALCULATE(SUM ('CurrentTimeEntry Details'[ActualHours]))

    Return x/Y

    Output I am getting:
     
    EmployeeProject1Project2Total
    A0.300.3
    B0.210.190.25(NOT CORRECT)
    C00.30.3
    Total0.510.491


    It is coming wrong as the measure is behaving at the backend like below ie ( Filtering across the project)
     
    Measure = 
    VAR Y=
    CALCULATE(SUMX(VALUES('CurrentTimeEntry Details'),[ActualHours]),ALLSELECTED('Staff Details'[Employee Name]),ALLSELECTED('Project Details'[Complete Project Detail]))  <--- Filtering

    VAR X = CALCULATE(SUM ('CurrentTimeEntry Details'[ActualHours]))

    Return x/Y

    Can you please suggest the solution as I am unable to avoid filtering and fix this
  • Tjeerd's avatar
    Tjeerd
    Icon for Helper I rankHelper I
    6 years ago

    Wow, thank you!

     

    I really needed this simplicity for something I am working on right now. I really was searching all over the place in very much too complex challenges and solutions. It really was as simple as the DAS you wrote down.