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.
With the help of the community, the following dax formula counts cells in a row with values = 0:
=IF (
HASONEVALUE ( raw_data[CDC (Coarse+Fine)]),
SUM ( raw_data[Value] ),
COUNTROWS (
FILTER ( VALUES (raw_data[CDC (Coarse+Fine)]) , CALCULATE ( SUM ( raw_data[Value] ) ) = 0 )
)
)
The far right column shows the count of cells for each row that contain 0.
I'd like to add another couple columns, that counts the zeroes before and after the yellow highlighted cell for each row.
I'm unsure how to adjust the formula to count the cells = 0 to only the range of [CDC (Coarse+Fine)] > 0.
Without having to construct a helper column.
https://drive.google.com/file/d/1tFF3WBXw8oUbkD92BiSUooqS4UYUVnoq/view?usp=sharing
Solved! Go to Solution.
Hi @roncruiser
Create measures
left 0 = IF ( HASONEVALUE ( raw_data[CDC (Coarse+Fine)]), SUM ( raw_data[Value] ), COUNTROWS ( FILTER ( VALUES(raw_data[CDC (Coarse+Fine)]), raw_data[CDC (Coarse+Fine)]<0&&CALCULATE ( SUM ( raw_data[Value] ) ) = 0 ))) right 0 = IF ( HASONEVALUE ( raw_data[CDC (Coarse+Fine)]), SUM ( raw_data[Value] ), COUNTROWS ( FILTER ( VALUES(raw_data[CDC (Coarse+Fine)]), raw_data[CDC (Coarse+Fine)]>0&&CALCULATE ( SUM ( raw_data[Value] ) ) = 0 ))) equal 0 = IF ( HASONEVALUE ( raw_data[CDC (Coarse+Fine)]), SUM ( raw_data[Value] ), COUNTROWS ( FILTER ( VALUES(raw_data[CDC (Coarse+Fine)]), raw_data[CDC (Coarse+Fine)]=0&&CALCULATE ( SUM ( raw_data[Value] ) ) = 0 )))
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @roncruiser
Create measures
left 0 = IF ( HASONEVALUE ( raw_data[CDC (Coarse+Fine)]), SUM ( raw_data[Value] ), COUNTROWS ( FILTER ( VALUES(raw_data[CDC (Coarse+Fine)]), raw_data[CDC (Coarse+Fine)]<0&&CALCULATE ( SUM ( raw_data[Value] ) ) = 0 ))) right 0 = IF ( HASONEVALUE ( raw_data[CDC (Coarse+Fine)]), SUM ( raw_data[Value] ), COUNTROWS ( FILTER ( VALUES(raw_data[CDC (Coarse+Fine)]), raw_data[CDC (Coarse+Fine)]>0&&CALCULATE ( SUM ( raw_data[Value] ) ) = 0 ))) equal 0 = IF ( HASONEVALUE ( raw_data[CDC (Coarse+Fine)]), SUM ( raw_data[Value] ), COUNTROWS ( FILTER ( VALUES(raw_data[CDC (Coarse+Fine)]), raw_data[CDC (Coarse+Fine)]=0&&CALCULATE ( SUM ( raw_data[Value] ) ) = 0 )))
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I need some assistance with the following measures you outlined for me last time.
For example, the follwing measure counts values where SUM (raw_data[value]) = 0 (highlighed in blue below).
left 0 = IF ( HASONEVALUE ( raw_data[CDC (Coarse+Fine)]), SUM ( raw_data[Value] ), COUNTROWS ( FILTER ( VALUES(raw_data[CDC (Coarse+Fine)]), raw_data[CDC (Coarse+Fine)]<0&&CALCULATE ( SUM ( raw_data[Value] ) ) = 0 )))
What I need to improve on the measure is to count values where SUM (raw_data[value] not equal to 0 and not equal to max for left 0.
In other words count values <> 0 and <> MAX. Meaning just count the values between 0 and MAX.
Which should look like something close to this but I cannot figure out how to count include <> MAX values.
left 0 = IF ( HASONEVALUE ( raw_data[CDC (Coarse+Fine)]), SUM ( raw_data[Value] ), COUNTROWS ( FILTER ( VALUES(raw_data[CDC (Coarse+Fine)]), raw_data[CDC (Coarse+Fine)]<0&&CALCULATE ( SUM ( raw_data[Value] ) )<> 0 )))
Thanks....
Adding photo will help with my explaination. Basically, for each row, counting the cells whose values do not equal 0 and do not equal MAX. Counting the values hightlight in green...
That's absolutely oustanding...
"<0&&"
">0&&"
"=0&&"
I kept trying to use the >,<,=, by themselves, in the wrong place, and not not preceding it with "raw_data[CDC(Coarse+Fine)].
I was doing:
FILTER ( VALUES(raw_data[CDC (Coarse+Fine)]) > 0, CALCULATE ( SUM ( raw_data[Value] ) ) = 0 )))
Some outstanding learning here.
Thank you.
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 |
---|---|
110 | |
97 | |
78 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |