Forum Discussion
Filter Field to Calculate on a Range
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
Hi Anonymous
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.
4 Replies
v-juanli-msftCommunity Support
Hi Anonymous
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.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.
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....