If, vlookup, match statements help

[Anonymous]

Hi,

I have one main table and two reference tables. I have created appropriate relationships between the reference tables and the main table.

I am trying to match a set of values (criterias) to the reference tables, if the set of values match on either reference tables then I want the output column to say "Pass" or "Fail" respective of the reference table. Please see example images below:

1) This is my main table

2) These are the two "Pass" and "Fail" reference tables:

Example

If the combination found in row 1 of the main table of 'Vendor', 'Company Code', and 'Product Code' exist in the "Pass" or "Fail" reference table then look at the 'Class/Unclass' column and assign "Pass" if classified or "Fail" if unclassified. 

How can I create this "Pass/Fail" column in my main data table?

Thanks for the help/guidance!!

[Anonymous · Accepted solution]

Hi  @Anonymous  ,

Here are the steps you can follow：

1. Create calculated column.

Pass/Fail =
IF(
    [Product Code] in SELECTCOLUMNS(ALL('Pass Ref Table'),"1",[Product Code])&&
    [Product Code] in SELECTCOLUMNS(ALL('Fail Ref Table'),"1",[Product Code])&&
    [Vendor] in SELECTCOLUMNS(ALL('Pass Ref Table'),"1",[Vendor ID])&&
    [Vendor] in SELECTCOLUMNS(ALL('Fail Ref Table'),"1",[Vendor ID])&&
    [Company Code]in SELECTCOLUMNS(ALL('Pass Ref Table'),"1",[Company Code])&&
    [Company Code]in SELECTCOLUMNS(ALL('Fail Ref Table'),"1",[Company Code]),
    IF(
        [Class/Unclass]="Classified","Pass","Fail"),
        "N/A")

2. Result:

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Try a new column in main table like

new column =
var _pass = countx(filter(pass, pass[vendorId] = main[vendor] && pass[company Code] = main[company Code] && pass[ProductCode] = main[ProductCode]), pass[vendorId])
var _fail = countx(filter(fail, pass[vendorId] = main[vendor] && fail[company Code] = main[company Code] && fail[ProductCode] = main[ProductCode]), fail[vendorId])
return
Switch(true(),
not(isblank(_pass)) , "Pass",
not(isblank(_pass)) , "Fail",
blank()
)