Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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 ) ) )
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 ) ) )
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...
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.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |