Finding if a value in table 1 has a matching value in table 2 AND both product category values match

[DigiS]

Hi,

I have two tables:

-Table1 (lookup table) named Matchlist, with columns Category, Keyword and Product

-Table2 (fact table) named ProductCategory, with columns ProductId and Product.

example Table1 (lookup table) named Matchlist:

Category | Keyword | Product

Asian | nasi | dinner
Italian | spaghetti | dinner
general | sandwich | lunch

example Table2 (fact table) named ProductCategory:

ProductId | Product
Spaghetti with meatballs and cheese | dinner
No Spaghetti but sandwich | lunch

I need classify each row in the fact table based on the productid in a product category (the category column). The category that should be added, is determined in the lookup table. However there is no exact macht possible, as keyword is only 1 or two words and productId more of a sentence. So what I trie to do is add the category value in the lookup table to the fact table (via a calculated column), when the value in the keyword column exists in the ProductId column of the fact table. This is what I menaged to do using the formula below.

Calculated column = 
var test9 = 
FIRSTNONBLANK(
    FILTER(
        VALUES(MatchList[Keyword]);
        SEARCH(
                Matchlist[Keyword];
                ProductCategory[ProductId];
                1;
                0
            )
    )
;1
)

var lookup =
LOOKUPVALUE(
    Matchlist[Product];
    Matchlist[Keyword];
    test9
)



return
if(test9 <> ""; 
    if (lookup = ProductCategory[Product]; test9; blank());
    blank()
)

However what I want to do is a bit more complex. I only want to add the keyword column value, if the product columns of both tables have an exact match. So in the examples above the row "No Spaghetti but sandwich | lunch" should not match with spaghetti (which it encounters first in the lookup table), but with sandwich because for sandwich both product columns match (apart from the existence of keyword in the productid).

I hope anyone can help me with this more advanced dax code.

[v-ljerr-msft]

Hi @DigiS,

If I understand you correctly, the formula below should also work in your scenario. 

Column =
CALCULATE (
    FIRSTNONBLANK ( Matchlist[Keyword]; 1 );
    FILTER (
        Matchlist;
        Matchlist[Product] = ProductCategory[Product]
            && SEARCH ( Matchlist[Keyword]; ProductCategory[ProductId];; 0 ) > 0
    )
)

Regards

[DigiS]

Hi @v-ljerr-msft,

Thanks for your reply!

This works, however in case of the "No Spaghetti but sandwich | lunch" row, the keyword that is retrieved is Spaghetti, because this value is higher in the Matchlist table. Where it should retreive the first match in the Match table where column product of both tables have an exact match. If it does this, than sandwich would be retreived as keyword (as it should do).

Hope this is a clear explanation and you can help me with integrating this in the calculated column.

[v-ljerr-msft]

Hi @DigiS,

Based on my understanding, the formula should work in your scenario. And sandwich is retrieved as keyword in case of the "No Spaghetti but sandwich | lunch" row in my test with your shared sample data. 

Regards

[DigiS]

Hi @v-ljerr-msft,

Thanks! You are right. Good job!

When there is more than one match in a row, it now retreives the first match based on alphabetical order (I assum based on a few tests). However I want to retreive the keyword that is first/highest in the Matchlist (or I can add a rank column to the Matchlist and retreive match with lowest rank number). So I have some more control over which match is shown in case there are more matches in a single row. Hope you can help me with writing this logic.