IF FILTER does not contain certain dates

[Austen229022]

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

[OwenAuger · Accepted solution]

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

[v-xuxinyi-msft · Accepted solution]

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:

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:

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 · Accepted solution]

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

[Austen229022]

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?