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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.