Measure to Count Values non-Zero and Non-Max by Row

[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 values other than 0 and Max before and after the yellow highlighted cell for each row.

For Example with this formula below, it counts all non-zeroe values for each row.  BUT I also do not want to count the Max values for each row.

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

I'm unsure how to adjust the formula to count the cells <>0 and <>Max to only the range of [CDC (Coarse+Fine)] < 0.  In other words count the cells in green below.  Max values are not counted and 0 values are not counted.

I've tried but cannot get this.  Very frustrating.

https://drive.google.com/file/d/1tFF3WBXw8oUbkD92BiSUooqS4UYUVnoq/view?usp=sharing

[v-chuncz-msft · Accepted solution]

@roncruiser ,

You may refer to the following measure.

Measure =
IF (
    HASONEVALUE ( raw_data[CDC (Coarse+Fine)] ),
    SUM ( raw_data[Value] ),
    VAR m =
        MAXX (
            FILTER (
                VALUES ( raw_data[CDC (Coarse+Fine)] ),
                raw_data[CDC (Coarse+Fine)] < 0
            ),
            CALCULATE ( SUM ( raw_data[Value] ) )
        )
    RETURN
        COUNTROWS (
            FILTER (
                VALUES ( raw_data[CDC (Coarse+Fine)] ),
                raw_data[CDC (Coarse+Fine)] < 0
                    && CALCULATE ( SUM ( raw_data[Value] ) ) <> 0
                    && CALCULATE ( SUM ( raw_data[Value] ) ) < m
            )
        )
)

[roncruiser]

Thanks for the help again @v-chuncz-msft .

Although, the task begins to grow for more data from the measure you helped me out with.

I need to begin to analyze the data created from the measure:

Basic stats like:

Min

Max 

Average

Since it is a measure, calculating the Max, Min and Ave from the values generated by the measure is not so straight forward using DAX...  to me.  I've tried.

Based on filtering, I can have many values generated from the measure or just a few.  I'd like to get the max or min or average without having to manually copy and paste the values to another table.

I understand MAXX and MINX or keys to max and min for a measure.  I am not clear on usage though.

Please help...  I'll post a new thread.

Thanks...

[roncruiser]

@v-chuncz-msft 

Thank you!  I do not have a handle on MAXX yet, but I did manage to find a way.

Alternately, since the data is just 1's and 0's, the count of value represents the max for each row.  That value I used in the equation to <> MemVexShmooFolder1_[Count of Value].  

Your way is much more elegant.  Mine was borne out of desperation, but it took me so long to figure out.

Measure =
=IF (
  HASONEVALUE ( MemVexShmooFolder1_[CDC (Coarse+Fine)]),
  SUM ( MemVexShmooFolder1_[Value] ),
  COUNTROWS (
      FILTER ( VALUES(MemVexShmooFolder1_[CDC (Coarse+Fine)]), MemVexShmooFolder1_[CDC (Coarse+Fine)]
      <0&&CALCULATE ( SUM ( MemVexShmooFolder1_[Value] ) ) 
      <>0 && CALCULATE ( SUM ( MemVexShmooFolder1_[Value] ) ) 
      <> MemVexShmooFolder1_[Count of Value] ))) 
   

Thank you again.