Forum Discussion
IF combination with LOOKUP
Hi arman_tale - check the below calculated column and change the table name as per your source.
Custom1 =VAR CurrentValue = 'Vlook'[Column2]VAR PriorityA = CALCULATE(MAX('Vlook'[Column2]), 'Vlook'[Column1] = EARLIER('Vlook'[Column1]) && 'Vlook'[Column2] IN {"A", "B"})VAR PriorityC = CALCULATE(MAX('Vlook'[Column2]), 'Vlook'[Column1] = EARLIER('Vlook'[Column1]) && 'Vlook'[Column2] IN {"C", "D"})VAR PriorityE = CALCULATE(MAX('Vlook'[Column2]), 'Vlook'[Column1] = EARLIER('Vlook'[Column1]) && 'Vlook'[Column2] IN {"E", "F"})
RETURNIF(CurrentValue IN {"A", "B"},PriorityA,IF(CurrentValue IN {"C", "D"},PriorityC,IF(CurrentValue IN {"E", "F"},PriorityE,BLANK())))Hope it works
rajendraongole1 SamWiseOwl
That works perfect, thank you. But I actually realized the problem I am facing is a bit deeper than that:
Let's say Column1 is independant variable, but I have dependant variables in groups of 2 (A and B), (C and D), (E and F). A has priority to B, C has priority to D and E has priority to F, and I need to show all the independant Column1 variables with the highest available priority variable in each group
I need my customcolumn to look like this
| Column1 | Column2 | Custom |
| 1 | B | A |
| 1 | A | A |
| 1 | C | C |
| 1 | D | C |
| 2 | A | A |
| 2 | D | C |
| 2 | C | C |
| 3 | F | F |
| 4 | E | E |
| 4 | F | E |
| 4 | B | B |
| 5 | A | A |
| 5 | B | A |
| 5 | C | C |
| 5 | D | C |
| 5 | E | E |
| 5 | F | E |
Appreciate your feedback and support!
Hi arman_tale - Create a new custom column in power query editor as below
Custom =
if [Column2] = "A" or [Column2] = "B" then "A"
else if [Column2] = "C" or [Column2] = "D" then "C"
else if [Column2] = "E" or [Column2] = "F" then "E"
else null
output:
This doesn't preceisely work the way I required. Look at the issue in the screenshot:
Any approach to overcome this issue?
Hi arman_tale - check the below calculated column and change the table name as per your source.
Custom1 =VAR CurrentValue = 'Vlook'[Column2]VAR PriorityA = CALCULATE(MAX('Vlook'[Column2]), 'Vlook'[Column1] = EARLIER('Vlook'[Column1]) && 'Vlook'[Column2] IN {"A", "B"})VAR PriorityC = CALCULATE(MAX('Vlook'[Column2]), 'Vlook'[Column1] = EARLIER('Vlook'[Column1]) && 'Vlook'[Column2] IN {"C", "D"})VAR PriorityE = CALCULATE(MAX('Vlook'[Column2]), 'Vlook'[Column1] = EARLIER('Vlook'[Column1]) && 'Vlook'[Column2] IN {"E", "F"})
RETURNIF(CurrentValue IN {"A", "B"},PriorityA,IF(CurrentValue IN {"C", "D"},PriorityC,IF(CurrentValue IN {"E", "F"},PriorityE,BLANK())))Hope it works
Love an example with Earlier!
Do you have any advice regarding when Earlier is better than storing (in this case) Column1 in its own variable?
