Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |