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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
MyPI
Regular Visitor

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

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:

TypeAvailabilityDate
SLS1-212/2/2022
C5H-112/1/2022
SLS1212/1/2022
SLS1-211/29/2022
SLS1-111/29/2022
C5H-111/29/2022
SLS1-111/25/2022
ECL211/25/2022

 

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

TypeAvailabilityDate
SLS1-212/2/2022
SLS1-211/29/2022
SLS1-111/29/2022
C5H-111/29/2022
SLS1-111/25/2022
ECL211/25/2022

 

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

TypeTotalAvailability
SLS1-6
C5H-1
ECL2

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?

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

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.

Jihwan_Kim_0-1675482056782.png

 

Jihwan_Kim_1-1675482313853.png

 

 

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

 

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

 


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

View solution in original post

2 REPLIES 2
Jihwan_Kim
Super User
Super User

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.

Jihwan_Kim_0-1675482056782.png

 

Jihwan_Kim_1-1675482313853.png

 

 

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

 

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

 


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

Hi Jihwan_Kim,

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.