Forum Discussion

roncruiser's avatar
roncruiser
Icon for Post Patron rankPost Patron
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

    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
            )
        )
)
v-chuncz-msft's avatar
v-chuncz-msft
Icon for Community Support rankCommunity Support
7 years ago

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's avatar
roncruiser
Icon for Post Patron rankPost Patron
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...