Forum Discussion
Helper ICountIf 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 counts the number of risks that that are more than 10 days overdue, but less than 5. How do i do that?
Thanks
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
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
4 Replies
- TomMartens
Super User
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
shaunparsons66Hi 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
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
Tom - You are my Lord and Saviour. This has worked perfectly - thank you!