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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Austen229022
Helper I
Helper I

IF FILTER does not contain certain dates

Hello, 

 

I am trying to create a COUNTROWS with a FILTER that has multiple critera. The criteria are that Decision Date has nothing in the column and that Case Date does not contain any date that is Christmas Day (25/12) in any year.

eg: 

CALCULATE(COUNTROWS('BSP'), FILTER('BSP', ISBLANK('BSP'[Decision Date]),'BSP'[Case Date] <> 25/12/2024))
 
This is coming up with errors and I am not sure why. Hopefully someone can help
2 ACCEPTED SOLUTIONS
OwenAuger
Super User
Super User

Hi @Austen229022 

I would recommend something like this. I have rewritten slightly to filter specific columns.

I also suggest using KEEPFILTERS if you want to intersect the filters with any existing filters.

 

YourMeasure =
CALCULATE (
    COUNTROWS ( 'BSP' ),
    KEEPFILTERS ( ISBLANK ( 'BSP'[Decision Date] ) ),
    KEEPFILTERS (
        NOT ( MONTH ( 'BSP'[Case Date] ), DAY ( 'BSP'[Case Date] ) ) IN { ( 12, 25 ) }
    )
)

 

Does this give the expected result?

 

I might also recommend adding a Date table for Case Date (at least) with a "flag" column to make filtering 25-Dec more convenient.

 

Regards


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

v-xuxinyi-msft
Community Support
Community Support

Hi @Austen229022 

 

Your solution is great, @OwenAuger . It worked like a charm! Here I have another idea in mind, and I would like to share it for reference.

 

First of all, I think the reason your formula is reporting an error is that the FILTER function can only contain two arguments.

FILTER function (DAX) - DAX | Microsoft Learn

 

Here is my testing:

My sample:

vxuxinyimsft_1-1712633126941.png

 

1. Create two measures as follow

Measure = 
IF (
    MAX ( [Decision Date] ) <> BLANK ()
        && MONTH ( MAX ( [Case Date] ) = 12 && DAY ( MAX ( [Case Date] ) ) = 25 ),
    0,
    1
)

 

COUNT =
CALCULATE ( COUNT ( BSP[Case Date] ), FILTER ( BSP, [Measure] = 1 ) )

 

Result:

 

vxuxinyimsft_0-1712632843542.png

 

Best Regards,
Yulia Xu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-xuxinyi-msft
Community Support
Community Support

Hi @Austen229022 

 

Your solution is great, @OwenAuger . It worked like a charm! Here I have another idea in mind, and I would like to share it for reference.

 

First of all, I think the reason your formula is reporting an error is that the FILTER function can only contain two arguments.

FILTER function (DAX) - DAX | Microsoft Learn

 

Here is my testing:

My sample:

vxuxinyimsft_1-1712633126941.png

 

1. Create two measures as follow

Measure = 
IF (
    MAX ( [Decision Date] ) <> BLANK ()
        && MONTH ( MAX ( [Case Date] ) = 12 && DAY ( MAX ( [Case Date] ) ) = 25 ),
    0,
    1
)

 

COUNT =
CALCULATE ( COUNT ( BSP[Case Date] ), FILTER ( BSP, [Measure] = 1 ) )

 

Result:

 

vxuxinyimsft_0-1712632843542.png

 

Best Regards,
Yulia Xu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

OwenAuger
Super User
Super User

Hi @Austen229022 

I would recommend something like this. I have rewritten slightly to filter specific columns.

I also suggest using KEEPFILTERS if you want to intersect the filters with any existing filters.

 

YourMeasure =
CALCULATE (
    COUNTROWS ( 'BSP' ),
    KEEPFILTERS ( ISBLANK ( 'BSP'[Decision Date] ) ),
    KEEPFILTERS (
        NOT ( MONTH ( 'BSP'[Case Date] ), DAY ( 'BSP'[Case Date] ) ) IN { ( 12, 25 ) }
    )
)

 

Does this give the expected result?

 

I might also recommend adding a Date table for Case Date (at least) with a "flag" column to make filtering 25-Dec more convenient.

 

Regards


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Thanks for the reply!

 

When it comes to setting the column I'd be looking for anything that is 25/12/... so what would be the best way to set that?

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.