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

Super User

2 years ago

Hi arman_tale
You could do this in the front end with a Calculated Column :

Custom Column =

var CurrentGroup = [Column1] --Capture group

var SameGroup = --Filter to same group

SELECTCOLUMNS(

Filter('Table', [Column1] = CurrentGroup)

,[Column2]) --return single column

RETURN

SWITCH(

TRUE()

,"A" IN SameGroup

,"A"

,"B" IN SameGroup

,"B"

,"null"

)