Power Bi - Lookup for A Value in Different Table (Reference Table) and Return True

Question

In Microsoft Power Bi

I have 850 products and a total of 15 vendors for these products.

I have two tables where the 1st table shows distinct list of products with only 8 vendors sorted from lowest to highest price (1 to 8):

and the second table shows the actual buying of every product with vendor name, quantity and price:

I need a column that checks if the vendor exists among the 8 choices for each product and return true. If not, it should return false.

Hope it's clear.

Thanks all :)

mkRabbani mkRabbani · Accepted Answer · 2020-11-16T17:46:07

You can use this below Measure for your purpose-

true_false = 

var current_row_product = MIN(Table2[product])
var current_row_vendor = MIN(Table2[vendor])

var find_vendor =  
CALCULATE(
    MAX(Table1[product]),
    FILTER(
        ALL(Table1),
        Table1[product] = current_row_product
        && current_row_vendor IN 
        {
            Table1[1st],
            Table1[2nd],
            Table1[3rd],
            Table1[4th],
            Table1[5th],
            Table1[6th],
            Table1[7th],
            Table1[8th]
        }
    )
)


RETURN IF(find_vendor = BLANK(), "FALSE", "TRUE")

Here below is the output-

Power Bi - Lookup for A Value in Different Table (Reference Table) and Return True

1 Answers