Filter Field to Calculate on a Range

[roncruiser]

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

[v-juanli-msft · Accepted 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.

[roncruiser]

@v-juanli-msft 

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....

[roncruiser]

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...

[roncruiser]

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.