Forum Discussion
Row comparison and calculation
Hi community,
I have 2 tables : the first one contains data measured (named "Valeur" below) and another one contains the max limit (named "Maxi" below).
What I need is :
1) Do a comparison between "Valeur" and "Maxi" for each rows in order to identify if my measure reachs a limit
2) I want to count how many measure reach its limits
Actually, my DAX formula with Calculate and Count function doesn't differenciate the different measure (named "Designation" below).
It returns 2 cause Etain and Cuivre are higher than 0.5 instead of returning 1 as the good result.
In fact, Cuivre is OK because Valeur 1.00 is lower than Maxi 1.5
Etain is not OK because Valeur 1.00 is higher than Maxi 0.5
Plomb is OK because Valeur 0.00 is lower than Maxi 2.0
Does anyone have an idea how to fix it ?
Thanks a lot
4 Replies
Anonymous I hope you are looking for something like this...
Please try this as a New Measure
Test290 = CALCULATE(DISTINCTCOUNT(Test290Measure[Designation]),FILTER(Test290Measure,Test290Measure[Value]>Test290Measure[MaxValue]))
Hello PattemManohar , thanks for your answer.
It was my first idea but unfortunately it didn't work.
First of all, we must add an agregation for the comparison to be validated by editor
> Min(Test290Measure[Maxvalue]
Then, by agregation MIN, PBI only considers the value 0.5 and compare it with the 3 others value (1.0 , 2.0 and 0.0) instead of comparing 3 times (for the 3 measures) Maxi and Valeur (1.0>1.5? 2.0>0.5? 0.0>2.0?)
So PBI return 2 (cause it see 1.0 and 2.0 are > 0.5) instead of resulting 1 (only 2.0>0.5).
Hope you understand my issue...
Anonymous Just to confirm, I've considered your sample data as table and performed the above measure logic which returned me 1 (As expected). Is the above screenshot of your sample data contains measures in that ?
