How to lookupvalue two different categories

[Gokan]

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	Rep	Perf. Crit.	Value	Performance
Jan	John Doe	Ziyaret	80%	Low Performance
Jan	John Doe	CLM	90%	Normal Performance
Jan	John Doe	Gerçekleşme	65%	Low Performance
Jan	John Doe	Pazar Index	75%	Normal Performance
Jan	John Doe	Gelişim Index	90%	X
Feb	John Doe	Ziyaret	90%	Normal Performance
Feb	John Doe	CLM	95%	Normal Performance
Feb	John Doe	Gerçekleşme	96%	Normal Performance
Feb	John Doe	Pazar Index	90%	Normal Performance
Feb	John Doe	Gelişim Index	93%

In this table, i want to look John Doe's - January - Gelişim Index value if it's below %100 AND John Doe's - January - Pazar Index value below %80 then it has to write the result in the table X

i'm newbie 🙂 so dont go harsh on me please 🙂

i wrote this but couldnt figured it out last performance 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";"Yüksek Performans")

Thanks.

[Gokan]

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?

[v-robertq-msft · Accepted solution]

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]

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

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.