Forum Discussion

Austen229022's avatar
Austen229022
Helper II
2 years ago
Solved

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
Need Help
Show and Tell
  • OwenAuger's avatar
    OwenAuger
    2 years ago

    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

  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

3 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    2 years ago

    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's avatar
      Austen229022
      Helper II
      2 years ago

      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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.