topic Re: How to count rows based on the group sums filtered by a date slicer? in DAX Commands and Tips

How to count rows based on the group sums filtered by a date slicer?

MyPI — Sat, 04 Feb 2023 02:54:50 GMT

Hi All,

I have a table which has three columns of Type, Availability and Date. I'd like to extract a subset of this table based on the selected range of the Date slicer. Then, compute the sum of Availability for each Type which could be either positive or negative. Finally, I want to count how many types which have a positive sum. I have searched around the forum and tried all kinds of methods, but none of them works. Please help, thanks!

Here is my input table:

Type	Availability	Date
SLS1	-2	12/2/2022
C5H	-1	12/1/2022
SLS1	2	12/1/2022
SLS1	-2	11/29/2022
SLS1	-1	11/29/2022
C5H	-1	11/29/2022
SLS1	-1	11/25/2022
ECL	2	11/25/2022

Here is how it is supposed to look like after filtered by the Date slicer.

Type	Availability	Date
SLS1	-2	12/2/2022
SLS1	-2	11/29/2022
SLS1	-1	11/29/2022
C5H	-1	11/29/2022
SLS1	-1	11/25/2022
ECL	2	11/25/2022

Here is how it is supposed to be after groupped by Type and computed the sum of Availability.

Type	TotalAvailability
SLS1	-6
C5H	-1
ECL	2

Here is what I want finally:
Number_Of_Types_With_Positive_TotalAvailability = 1

Any idea about how to write the DAX function to derive the Measure Number_Of_Types_With_Positive_TotalAvailability?

Re: How to count rows based on the group sums filtered by a date slicer?

Jihwan_Kim — Sat, 04 Feb 2023 03:45:47 GMT

Hi,

I am not sure how your datamodel and slicer looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

Total availability: = IF ( HASONEVALUE ( 'Type'[Type] ), SUM ( Data[Availability] ) )

Number_Of_Types_With_Positive_TotalAvailability: = COUNTROWS ( FILTER ( DISTINCT ( 'Type'[Type] ), [Total availability:] > 0 ) )

Re: How to count rows based on the group sums filtered by a date slicer?

MyPI — Sun, 05 Feb 2023 17:54:47 GMT

Hi Jihwan_Kim,

The solution you recommended does work! Thank you very much for your help!