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