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!

     

     

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