Forum Discussion

shaunparsons66's avatar
shaunparsons66
Icon for Helper I rankHelper I
4 years ago
Solved

CountIf based on two filters

Hi all - Would be grateful for some guidance on what I think is an easy measure.   I have the following table of data:   Risk ID Days of Overdue (number)   I need to create a measure that co...
Need Help
Show and Tell
  • TomMartens's avatar
    TomMartens
    4 years ago

    Hey shaunparsons66 ,

     

    please take the time and create a pbix file that contains some sample data, but still reflects your data model (tables, relationships, calculated columns, and measures.) Upload the pbix to onedrive or dropbox and share the link. If you are using Excel to create the sample data instead of the manual input method share the xlsx as well. Describe the expected result based on the sample data you provide.

     

    To me, the result of the measure is not clear if a Risk id has been selected that is 3 or 12 days overdue. It's also not clear if you are looking for a general measure that will be used on a special visual like a Card visual, a table/matrix visual, or will be used on any visual. This is important information, as the filter context created by slicers and the visual itself may or may not impact the result of the measure.

     

    Regards,

    Tom

  • TomMartens's avatar
    TomMartens
    4 years ago

    Hey shaunparsons66 ,

     

    I assume this measure provides what you are looking for:

    no of Risk IDs = 
COUNTROWS(
    FILTER(
        'Risks'
        , 'Risks'[Days Overdue] >= -30 && 'Risks'[Days Overdue] <= 0
    )
)

    The following picture shows the result when the measure is used on a Card visual:

    This is reflected by the number of records in the data view:

     

    Hopefully, this provides what you are looking for.

     

    Regards,

    Tom

shaunparsons66's avatar
shaunparsons66
Icon for Helper I rankHelper I
4 years ago

Hi Tom

 

I've managed to upload the file to DropBox - here is the link: https://www.dropbox.com/s/1qn8gojv61e0stj/Risks-Date-Count.pbix?dl=0

 

To be more detailed about what I need, I want to create a measure that returns the number of rows where the 'Days Overdue' number value falls between two values.

 

For example, I'd like to create a measure that tells me how many rows there are where the 'Days Overdue' value is between -30 and 0. However, DAX doesn't seem to recognise the negative numbers.

TomMartens's avatar
TomMartens
Icon for Super User rankSuper User
4 years ago

Hey shaunparsons66 ,

 

I assume this measure provides what you are looking for:

no of Risk IDs = 
COUNTROWS(
    FILTER(
        'Risks'
        , 'Risks'[Days Overdue] >= -30 && 'Risks'[Days Overdue] <= 0
    )
)

The following picture shows the result when the measure is used on a Card visual:

This is reflected by the number of records in the data view:

 

Hopefully, this provides what you are looking for.

 

Regards,

Tom