Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
User | Count |
---|---|
51 | |
36 | |
20 | |
14 | |
13 |
User | Count |
---|---|
94 | |
72 | |
30 | |
21 | |
13 |