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

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)
    )