Forum Discussion
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...
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
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"})
RETURN
IF(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?Thank you SamWiseOwl , yes when you need to perform outer row context manipulation,while you are inside a nested row context in particularly when you're working with nested row contexts in the above scenerios, it work fine.
arman_tale - please check
Thank you
