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.

Gokan's avatar
Gokan
Frequent Visitor
5 years ago

Hi v-robertq-msft 
Thanks for great reply! I've downloaded your file and works like a charm.

I tried to add my formula but it didn't worked for me, i think i'm doing something wrong.

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(var _filter1= FILTER('Sonuçlar';'Sonuçlar'[Value]&&MONTH('Sonuçlar'[Dönem].[Month])&&'Sonuçlar'[Attribute]="Pazar Index"&&'Sonuçlar'[Value]<0,8) var _filter2= FILTER('Sonuçlar';'Sonuçlar'[Value]&&MONTH('Sonuçlar'[Dönem].[Month])&&'Sonuçlar'[Attribute]="Gelişim Index"&&'Sonuçlar'[Value]<1) return UNION(_filter1;_filter2)))


In your formula i've to edit "month" section, because we have to look "current month" but i'm not sure this is right or wrong.


Here, it's looking all 5 criterias (last criteria is looking two criterias as i mentioned)

 

What is wrong in my formula?