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

Icon for Super User rank

Super User

2 years ago

Hi arman_tale -you can do group by Column1, and for the Column2 column, create an "All Rows" operation (this will create a table for each Column1).

add one new column (Custom column) with below if condition as :

if Table.Contains([AllRow], [Column2 = "A"]) then "A"
else if Table.Contains([AllRow], [Column2 = "B"]) then "B"
else null

Expand Allrows column2

output:

Hope it helps