Row comparison and calculation

[Anonymous]

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

[PattemManohar]

@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]))

[Anonymous]

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

[PattemManohar]

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

[Anonymous]

Non, no measures added to the table.

I think I understand the problem.

As I explain on my first post, the column "valeur" come from one data table "Donnés" and the column "maxi" come from another data table called "Objectifs".

These 2 tables have common filter "Moteur" and "Designation" (Related) which could allow direct comparison between "Valeur" and "Maxi".

But as I can see, it's not really what happen, I don't know why... Maybe too complex ?