Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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

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.

Capture.JPG

 

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.

 

count_fuzz.JPG

 

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

 

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

 

Need Help
  • v-chuncz-msft's avatar
    v-chuncz-msft
    7 years ago

    Anonymous ,

     

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

3 Replies

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Anonymous ,

     

    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
            )
        )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

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