Forum Discussion

arman_tale's avatar
arman_tale
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...
Need Help
  • rajendraongole1's avatar
    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

     

SamWiseOwl's avatar
SamWiseOwl
Icon for Super User rankSuper User
2 years ago

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?

rajendraongole1's avatar
rajendraongole1
Icon for Super User rankSuper User
2 years ago

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