Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
jj_0511
Helper I
Helper I

How filter argument in CALCULATE interact with outer filter context on different col in same table

Hello:

 

I have two tables:

 

Date:
sample data:

DateMonth Year

2023-01-01

   Jan 2023

2023-01-02   Jan 2023
...   ...

 

Sales:

DateSales
2023-01-01   9
2023-02-01   3
2023-03-01   3
2023-04-01   4
2023-05-01   2

 

I have a measure:

Sales _ =
CALCULATE(
    SUM(Sales[Sales])
    , 'Date'[Date] = DATE(2023,5,1)
    )
 
I created a table based on 'Date'[Month Year] and measure [Sales_] which shows [Sales_] has same value for all [Month Year] which is sales for DATE(2023,5,1). 
 
jj_0511_0-1686862099334.png

Why [Sales_] has same value for all [Month Year] in the table?

Please find pbix here: pbix sample 

Thanks.

 
 
11 REPLIES 11
Ahmedx
Super User
Super User

just your measure removes all filters
read about it here:
https://www.sqlbi.com/articles/introducing-calculate-in-dax/
https://youtu.be/HxZLkmpY6BA?t=270

 

your measure :
CALCULATE(
    SUM(Sales[Sales])
    , 'Date'[Date] = DATE(2023,5,1)
------ 
its complete syntax 
CALCULATE(
    SUM(Sales[Sales])
    , FILTER(ALL('Date'), 'Date'[Date] = DATE(2023,5,1))

 

The complete syntax should be:

CALCULATE(
    SUM(Sales[Sales])
    , FILTER(ALL('Date'[Date]), 'Date'[Date] = DATE(2023,5,1))

Still calculate filter and outer filter are on different columns in same table.

jj_0511
Helper I
Helper I

Can someone else or someone from Microsoft confirm this:  filter condition of the calculate function over writes the outer filter condition even when they are on different columns in same table?

Any experts here can shed some light on this?

Ashish_Mathur
Super User
Super User

Hi,

That is because the filter condition of the CALCULATE() function is overriding the implicit filter of the row labels.  What result are you expecting?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur The result I'm expecting is that there's value for only May 2023 and other rows should be blank since inner and outer filter context match only for May 2023. 
Outer filter is on 'Date'[Month Year] and inner filter is on 'Date'[Date], so my understanding is that in this case inner filter does not override outer filter since they are on different columns in same table.
Could you expand on "overriding the implicit filter of the row labels" ? where can I find more info on this?

Hi,

Either of these measures works.  If you want to see the other months as well, you may wrap these functions in the COALESCE() function and specify the second argument as 0.

Sales _ = CALCULATE(SUM(Sales[Sales]), KEEPFILTERS('Date'[Date]=DATE(2023,5,1)))
Sales _1 = CALCULATE(SUM(Sales[Sales]), FILTER(values('Date'[Date]),'Date'[Date]=DATE(2023,5,1)))

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks, but my questions is why the following measure shows the same value for all Year Month:

 

Sales _ =
CALCULATE(
    SUM(Sales[Sales])
    , 'Date'[Date] = DATE(2023,5,1)
    )
 
Outer filter is on 'Date'[Month Year] and inner filter is on 'Date'[Date], so my understanding is that in this case inner filter does not override outer filter since they are on different columns in same table. So I expect the result using the above measure should be blank except  for May 2023.

Hi,

If there is a conflict between the inner and outer filter (which is clearly your case), then the filter condition of the calculate function over writes the outer filter condition.  To reverse this, you may either use the KEEPFILTER() or the FILTER() function.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

So the filter condition of the calculate function over writes the outer filter condition even when they are on different columns? Is this behavior documented anywhere?

Yes, that is what it looks like.  Not sure of where this is documented.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.