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!

     

     

tananich's avatar
tananich
Frequent Visitor
7 years ago

Hi, has anyone solved this?

I'm having smth similar in PowerBI,  I need to completely ignore one of columns in my measure, using DAX.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    The same thing.

    It's very simple  work by fixed lod in Tableau but in PBI...

     

    BR,

    Anastasiia Lagunova

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      7 years ago

      Anonymous, Anonymous, tananich, manito969, empires - OK, I believe that this is an issue that marcorusso and I took a look at in another thread and Marco wrote up a nice blog article about. I have the fix in the attached PBIX. Basically, take ShippingDate and create another table using DISTINCT('Table'[ShippingDate]) Then relate the tables. Use this other table as your slicer and change your measure to refer to this new table. As Marco explains, this is not technically a bug but a pre-filtering "feature" of DAX.

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi Greg,
        Fairly new to PBI and trying to incorporate your solution into my measure, and I can't work out where I'm going wrong, please could you take a look at the below? Essentially trying to make this measure ignore the month filter on the page and pull back the full year's budget

        NET_PAIRS_FY_TOTAL_BUD/FOR_RetRocks =
        CALCULATE(
            SUM,('FP&A Profit & Loss Data'[QUANTITY]),
            ALL('FP&A Profit & Loss Data'[MONTH]),
            FILTER('FP&A Profit & Loss Data',
            'FP&A Profit & Loss Data'[TIER_2] = "Group Underlying Operating"
            && 'FP&A Profit & Loss Data'[LEVEL_THREE_BRANCH_ACCOUNTS] IN {"Gross Sales", "Returns"}
            && 'FP&A Profit & Loss Data'[FOOTWEAR] IN {"Footwear"}
            && 'FP&A Profit & Loss Data'[REGION] IN {"Americas", "EMEA", "Greater China", "India", "Japan & Korea", "South East Asia", "UK & ROI", "Region Adjustment"}
            && 'FP&A Profit & Loss Data'[VERSION] = SELECTEDVALUE('version_sort'[Version])
            && 'FP&A Profit & Loss Data'[YEAR] = 2023)
        )
  • charleshale's avatar
    charleshale
    Icon for Continued Contributor rankContinued Contributor
    6 years ago

    Can you try calculate ( [measure], allexcept ( .......the one item you're trying to have work as a filter....)?

     

    And if you need to, you could go to the table that your filtering against and add custom columns to allow easier filters

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    I have the same problem - trying to calculate the Fiscal Budget for a year and need to ignore the month filter ( YYYY,MM are the filters). Why can't Microsoft make is easy like Tableau for standard time functions ?

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks, Greg the solution you proposed works. Really counterintuitive if I have to define the measure on the Date dimension table.

      For now it works and that's all I care about