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

     

rajendraongole1's avatar
rajendraongole1
Icon for Super User rankSuper 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:

 

 

arman_tale's avatar
arman_tale
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?

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    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