MATCH THE DATA FROM ONE COLUMN TO ANY OF THE DATA IN ANOTHER COLUMN IN ONE TABLE

[RaineJ]

Ive been trying to do this logic where if the age match any of the value in the first column it will result the respective value in the second up to seventh column.

Ive been trying this code

Status =

    IF('Measure'[AGE]='Measure'[AIM]&&'Measure'[WEIGHT]<='Measure'[SUW_G],"SUW",

    IF('Measure'[AGE]='Measure'[AIM]&&'Measure'[WEIGHT]>='Measure'[UW+_G] || 'Measure'[WEIGHT] <= 'Measure'[UW-_G],"UW",

    IF('Measure'[AGE]='Measure'[AIM]&&'Measure'[WEIGHT]<='Measure'[N+_G] || 'Measure'[WEIGHT] <= 'Measure'[N-_G],"N",

    IF('Measure'[AGE]='Measure'[AIM]&&'Measure'[WEIGHT]>='Measure'[OW_G],"OW"

    ))))

the problem is example if the first cell in 2 different column matched thats when it will only show the right result.

Is there any way or condition where wherever I type the number as long as that number is available in the first cell it will result the right value?

[tamerj1 · Accepted solution]

Hi @RaineJ 
To be honest, I'm not sure if I correctly understand the requirement. However, this is a rather complex solution for you to check from your end. Please refer to attached sample file.

Status = 
VAR CurrentTable = 
    SELECTCOLUMNS ( 
        FILTER ( 'Table', 'Table'[AIM] = EARLIER ( 'Table'[AGE] ) ), 
        "SUW", [SUW_G], "UW",  [UW+_G], "UW-", [UW-_G], "N", [N+_G], "N-", [N-_G], "OGW", [OW_G], "AGE", [AGE] 
    )
VAR String1 = TOCSV ( CurrentTable, 1 )
VAR Items1 = SUBSTITUTE ( String1, UNICHAR ( 10 ), "|" )
VAR String2 = PATHITEM ( Items1, 1 )
VAR Items2 = SUBSTITUTE ( String2, ",", "|" )
VAR Length = COALESCE ( PATHLENGTH ( Items2 ), 1 )
VAR String3 = PATHITEM ( Items1, 2 )
VAR Items3 = SUBSTITUTE ( String3, ",", "|" )
VAR T1 = GENERATESERIES ( 1, Length, 1 )
VAR T2 = ADDCOLUMNS ( T1, "@Name", PATHITEM ( Items2, [Value] ), "@Value", VALUE ( PATHITEM ( Items3, [Value] ) ) )
VAR T3 = FILTER ( T2, [@Value] = 'Table'[WEIGHT] )
VAR ColumnName = SUBSTITUTE ( MAXX ( T3, [@Name] ), "-", "" )
VAR Result = SUBSTITUTE ( SUBSTITUTE ( ColumnName, "[", "" ), "]", "" )
RETURN
    Result

[RaineJ]

Thank you sir it already works for me. i just got confused with my data.

[RaineJ]

thank you sir but it dont work for me when i input different age.

[tamerj1]

@RaineJ 

Would you please explain further? What do you by input different age?

[RaineJ]

My bad sir. As I tried your code I only got the correct prediction in 0 but when I input different age like 1,2 55,56, and 60. It didnt show any prediction.

[tamerj1]

@RaineJ 

As you can see in my sample file I have used 3 different values for the AGE column and it worked just fine. Therefore I believe I don't fully understand the logic. Can we connect now via teams or zoom perhaps to clarify further?

[wdx223_Daniel]

Status =

    IF('Measure'[AGE]='Measure'[AIM],SWITCH(TRUE(),'Measure'[WEIGHT]<='Measure'[SUW_G],"SUW",'Measure'[WEIGHT]>='Measure'[UW+_G] && 'Measure'[WEIGHT] <= 'Measure'[UW-_G],"UW",'Measure'[WEIGHT]<='Measure'[N+_G]&&'Measure'[WEIGHT] <= 'Measure'[N-_G],"N",

    'Measure'[WEIGHT]>='Measure'[OW_G],"OW"

    ))

[RaineJ]

Thank you, but i still have the same problem.
 it only show result if the cell in the same row matched

[wdx223_Daniel]

Status =

VAR _tbl=FILTER('Measure','Measure'[AGE]=EARLIER('Measure'[AIM]))

RETURN

    SWITCH(TRUE(),'Measure'[WEIGHT]<=MAXX(_tbl,'Measure'[SUW_G]),"SUW",'Measure'[WEIGHT]>=MAXX(_tbl,'Measure'[UW+_G]) && 'Measure'[WEIGHT] <= MAXX(_tbl,'Measure'[UW-_G]),"UW",'Measure'[WEIGHT]<=MAXX(_tbl,'Measure'[N+_G])&&MAXX(_tbl,'Measure'[WEIGHT]) <= 'Measure'[N-_G],"N",

    'Measure'[WEIGHT]>=MAXX(_tbl,'Measure'[OW_G]),"OW"

    )

[RaineJ]

im really thankful sir. but it is still the same in my case. 

the first row only got the right prediction while the rest, output the "OW".

im a beginner with this so im wondering if its possible.

[wdx223_Daniel]

Status =
VAR _tbl =
    FILTER ( 'Measure', 'Measure'[AGE] = EARLIER ( 'Measure'[AIM] ) )
RETURN
    SWITCH (
        TRUE (),
        'Measure'[WEIGHT] <= MAXX ( _tbl, 'Measure'[SUW_G] ), "SUW",
        'Measure'[WEIGHT] >= MAXX ( _tbl, 'Measure'[UW+_G] )
            && 'Measure'[WEIGHT] <= MAXX ( _tbl, 'Measure'[UW-_G] ), "UW",
        'Measure'[WEIGHT] <= MAXX ( _tbl, 'Measure'[N+_G] )
            && MAXX ( _tbl, 'Measure'[WEIGHT] ) <= 'Measure'[N-_G], "N",
        'Measure'[WEIGHT] >= MAXX ( _tbl, 'Measure'[OW_G] ), "OW"
    )

i got a wrong filter condition

[RaineJ]

the result still the same sir.