Measure with ALL or REMOVEFILTER not working

[KaroRoza]

Hello,

I have Sales Table for years/months with Dim Table Date.

In Sales Table I calculated column which only for selected countries populate Margin for 2023 or 2024 (it is project requirement, it is benchmark). It is simply conditional column.

Then in dashboard I have Slicer: Year ( from Dim Date).

I have measure which I would like to show values not matter which year I choose.

I try:

Base = CALCULATE(sum( 'Sales'[Benchmark 2023 or 2024]),all(DimDate[YEAR]))

Base = CALCULATE(sum( 'Sales'[Benchmark 2023 or 2024]),REMOVEFILTERS(DimDate[YEAR]))

In case in Slicer I select 2025:

then I am having 0 values, when I select 2024 or 2023 aslo values are not show correctly.

Basically, I want to populate [Benchmark 2023 or 2024] (which are values from 2023 and 2024) no matter which Year is selected in Slicer. 

[Praful_Potphode]

Hi @KaroRoza 

try below:

Base =
CALCULATE(
    SUM('Sales'[Benchmark 2023 or 2024]),
    ALL('DimDate'),
    KEEPFILTERS('DimDate'[Month])
)

or this one

Base =
CALCULATE(
    SUM('Sales'[Benchmark 2023 or 2024]),
    ALL('DimDate'),
    VALUES('DimDate'[Month])
)

Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

[grazitti_sapna]

Hi @KaroRoza 

To fix this, you just need to explicitly filter your measure to the years you want like below, please try this and let us know if it works:

Base = CALCULATE(SUM('Sales'[Benchmark 2023 or 2024]), FILTER(ALL(DimDate[YEAR]), DimDate[YEAR] IN {2023, 2024}))

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.

💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.

🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.

🔗 Curious to explore more? [Discover here].

Let’s keep building smarter solutions together!

[v-sshirivolu]

Hi @KaroRoza ,

I would also take a moment to thank @grazitti_sapna  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions

[KaroRoza]

hi,

@v-sshirivolu , @grazitti_sapna

When I apply:

Base = CALCULATE(SUM('Sales'[Benchmark 2023 or 2024]), FILTER(ALL(DimDate[YEAR]), DimDate[YEAR] IN {2023, 2024}))

it is not working as expected. When I choose 2024 from slicer it populates only values for 2024 year ( but few countries have benchmark for 2023 -> it is showing 0 for those countires).

Basically I would like to have column which shows static values from 2023/2024 not matter which year I select in slicer. There is also Month slicer, which should work, as Benchmark depends on months selected.

Would you be bale to help me with it?

[v-sshirivolu · Accepted solution]

Hi @KaroRoza ,

Thanks for the follow-up and for explaining your scenario clearly. The issue happens because using ALL(DimDate[Year]) clears the entire date context, that’s why your Month slicer stops behaving as expected.

Try using this version instead:

Base =
CALCULATE(
SUM('Sales'[Benchmark 2023 or 2024]),
REMOVEFILTERS(DimDate[Year])
)

This keeps the Month slicer active while ignoring the Year slicer, so your measure always reflects 2023 - 2024 data regardless of the selected year.

Best regards,
Sreeteja.

[v-sshirivolu]

Hi @KaroRoza ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

[pankajnamekar25]

Hello @KaroRoza 

try this

You need to remove all filters from the Date table so your measure ignores slicer selections completely

Base =
CALCULATE(
SUM('Sales'[Benchmark 2023 or 2024]),
ALL('DimDate')
)

This removes all filters from the entire Date dimension (not just Year), ensuring that even if 2025 is selected, the measure still considers Sales rows for 2023 and 2024 benchmarks.

If my response helped you, please consider clicking
Accept as Solution ✅ and giving it a Like 👍 – it helps others in the community too.


Thanks,


Connect with me on:

LinkedIn

[KaroRoza]

thanks @pankajnamekar25 !!

It works... but.. I have also Slicer for Month - which I would like to use for that visual.

Could you help me with measure  which should not change on Years, but on Month yes.

Many thanks!!