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

     

arman_tale's avatar
arman_tale
New Member
2 years ago

rajendraongole1 SamWiseOwl 
That works perfect, thank you. But I actually realized the problem I am facing is a bit deeper than that:

Let's say Column1 is independant variable, but I have dependant variables in groups of 2 (A and B), (C and D), (E and F). A has priority to B, C has priority to D and E has priority to F, and I need to show all the independant Column1 variables with the highest available priority variable in each group

I need my customcolumn to look like this

 

Column1Column2Custom
1BA
1AA
1CC
1DC
2AA
2DC
2CC
3FF
4EE
4FE
4BB
5AA
5BA
5CC
5DC
5EE
5FE

 

Appreciate your feedback and support!

  • rajendraongole1's avatar
    rajendraongole1
    Super 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
        Super 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