IF combination with LOOKUP

[arman_tale]

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 with the priority of A and if NOT, find B, if NOT show null.

Examplle:

Column1	Column2	CustomColumn
1	B	A
1	A	A
2	A	A
2	B	A
3	A	A
4	B	B
5	B	B
6	C	B
6	B	B
7	C	null

Highly appreciate your support!

[ryan_mayu]

@arman_tale 

you can try this

Column =

VAR _p=CALCULATE(min('Table'[Column2]),ALLEXCEPT('Table','Table'[Column1]))

return if (_p="A", "A",if(_p="B","B",blank()))

[arman_tale]

@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

Column1	Column2	Custom
1	B	A
1	A	A
1	C	C
1	D	C
2	A	A
2	D	C
2	C	C
3	F	F
4	E	E
4	F	E
4	B	B
5	A	A
5	B	A
5	C	C
5	D	C
5	E	E
5	F	E

Appreciate your feedback and support!

[rajendraongole1]

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]

@rajendraongole1 

This doesn't preceisely work the way I required. Look at the issue in the screenshot:

Any approach to overcome this issue?

[rajendraongole1 · Accepted solution]

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]

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]

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

[rajendraongole1]

Hi @arman_tale -you can do group by Column1, and for the Column2 column, create an "All Rows" operation (this will create a table for each Column1).

add one new column (Custom column) with below if condition as :

if Table.Contains([AllRow], [Column2 = "A"]) then "A"
else if Table.Contains([AllRow], [Column2 = "B"]) then "B"
else null

Expand Allrows column2

output:

Hope it helps

[SamWiseOwl]

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"

 )