Forum Discussion

New Member

2 years ago

Solved

IF combination with LOOKUP

Hi all, Let's assume I have the following table Column1 Column2 1 B 1 A 2 A 2 B 3 A 4 B 5 B 6 C 6 B 7 C I need to create a c custom column w...

rajendraongole1
2 years ago
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"})

RETURN

IF(CurrentValue IN {"A", "B"},

PriorityA,

IF(CurrentValue IN {"C", "D"},

PriorityC,

IF(CurrentValue IN {"E", "F"},

PriorityE,

BLANK())))

Hope it works

rajendraongole1

Super User

2 years ago

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:

New Member

2 years ago

rajendraongole1

This doesn't preceisely work the way I required. Look at the issue in the screenshot:

Any approach to overcome this issue?