Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
roncruiser
Helper V
Helper V

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

 

1 ACCEPTED SOLUTION
v-chuncz-msft
Community Support
Community Support

@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
            )
        )
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-chuncz-msft
Community Support
Community Support

@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
            )
        )
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.