Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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 ) )
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.
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 ) )
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.
Hi Jihwan_Kim,
The solution you recommended does work! Thank you very much for your help!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
51 | |
45 | |
15 | |
12 |