Forum Discussion
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 | 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
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.
4 Replies
ryan_mayuSuper User
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]))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 thenI'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.
- v-robertq-msft
Community Support
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.
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?
