Forum Discussion

Gokan's avatar
Gokan
Frequent Visitor
5 years ago
Solved

How to lookupvalue two different categories

Hey there,   We have 5 different performance categories; * Ziyaret * CLM * Gelişim Index * Gerçekleşme * Pazar Index   4 of them have single target but pazar index has 2 target.   Month...
Need Help
Show and Tell
  • v-robertq-msft's avatar
    v-robertq-msft
    5 years ago

    Hi, Gokan 

    According to your description in the first reply, you can try this calculated table to write the result in table X:

    table X =

var _filter1=

FILTER('Sonuçlar',[Rep]="John Doe"&&[Month]="Jan"&&[Perf. Crit.]="Gelişim Index"&&[Value]<1)

var _filter2=

FILTER('Sonuçlar',[Rep]="John Doe"&&[Month]="Jan"&&[Perf. Crit.]="Pazar Index"&&[Value]<0.8)

return

UNION(_filter1,_filter2)

     

    And you can get what the table like this:

     

    For the formula in your second reply, I think it don’t have any problem. It’s a good solution.

     

    You can download my test pbix file here

     

    Best Regards,

    Community Support Team _Robert Qin

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper User
5 years ago

Gokan 

plesae try this and combine this one with your current DAX.

Column = 
VAR pazar=maxx(FILTER('Table','Table'[Month]=EARLIER('Table'[Month])&&'Table'[Rep]=EARLIER('Table'[Rep])&&'Table'[Perf. Crit.]="Pazar Index"),'Table'[Value])
return if('Table'[Perf. Crit.]="Gelişim Index",if('Table'[Value]<1&&pazar<0.8,'Table'[Value]))

 

Gokan's avatar
Gokan
Frequent Visitor
5 years ago

Hello ryan_mayu ,

 

I think i couldnt make the dax, in my opinion there is mistake here;

Column =
VAR pazar=maxx(FILTER('Table','Table'[Month]=EARLIER('Table'[Month])&&'Table'[Rep]=EARLIER('Table'[Rep])&&'Table'[Perf. Crit.]="Pazar Index"),'Table'[Value])
return if('Table'[Perf. Crit.]="Gelişim Index",if('Table'[Value]<1&&pazar<0.8,'Table'[Value]))

 

I've solved from another way but is it too long way or the shortcut i'm not sure;

 

* I've created a column and dax formula is: 

Column = LOOKUPVALUE('Sonuçlar'[Value];'Sonuçlar'[Attribute];"Gelişim Index";'Sonuçlar'[Dönem];'Sonuçlar'[Dönem];'Sonuçlar'[TM];'Sonuçlar'[TM])
 
It's looking same person and same month with the result of "Gelişim Index"
and then
 
I've added the main formula;
 
Performans Durumu = SWITCH(TRUE();
AND('Sonuçlar'[Attribute]="Ziyaret";'Sonuçlar'[Value]<0,8);"Düşük Performans";
AND('Sonuçlar'[Attribute]="CLM";'Sonuçlar'[Value]<0,7);"Düşük Performans";
AND('Sonuçlar'[Attribute]="Gerçekleşme";'Sonuçlar'[Value]<0,8);"Düşük Performans";
AND('Sonuçlar'[Attribute]="Gelişim Index";'Sonuçlar'[Value]<0,8);"Düşük Performans";
AND('Sonuçlar'[Attribute]="Pazar Index";'Sonuçlar'[Value]<0,8)&&'Sonuçlar'[Column]<1;"Düşük Performans";"Normal Performans")
 
bold one is the referring the column i created.