Combine Company Products & Competitive products in one table using DAX

[Anonymous]

Hello Power BI Community,

I have access to retail market basket data that shows products bought alongside our products. A consumer may buy our products or competitive products along with the original product.  I would like to have a master catalog that combines all of these products and descriptions into a new table.  We should use the original description when available and use market basket product one when missing.  I would like to accomplish this using DAX.

Here is a link to the PBIX file.

I appreciate any help to get the new combined table created.

[Anonymous]

I was able to get a unique list of products using the following DAX for a new table:

Unique_Items = union(VALUES(Product_Catalog[item),VALUES(market_basket[item_2]),VALUES(market_basket[item_3]))

However, I still need to add a unique set of description, and hopefully price with a bias towards product_catalog. 

[MFelix]

Hi @Anonymous ,

Try the following code for your table:

Unique_Items =
UNION (
    GROUPBY (
        ALL ( Product_Catalog[item]; Product_Catalog[description] );
        Product_Catalog[item];
        Product_Catalog[description]
    );
    GROUPBY (
        ALL ( market_basket[description2]; market_basket[item_2] );
        market_basket[item_2];
        market_basket[description2]
    );
    GROUPBY (
        ALL ( market_basket[item_3]; market_basket[description3] );
        market_basket[item_3];
        market_basket[description3]
    )
)

Regards,

MFelix

[Anonymous]

Thanks for the response @MFelix  The items are repeated if they are present in both 'Market Basket' & 'Product Catalog' I assume because they have different descriptions. Is there a way to use the descriptions from the product catalog table if available and from Market basket if not for a unique list of combined items? If we have to create another 'helper' table as another step that would be ok as well. 

[MFelix · Accepted solution]

Hi @Anonymous ,

to simplify the process and you understand better where the values are coming from in the future create a table with the following code:

Unique_Items = 
 
UNION (
    GROUPBY (
        ALL ( Product_Catalog[item]);
        Product_Catalog[item]
    );
    GROUPBY (
        ALL (  market_basket[item_2] );
        market_basket[item_2]
    );
    GROUPBY (
        ALL ( market_basket[item_3] );
        market_basket[item_3]
    )
)

Then add a calculated column with the following code:

DESCRIPTION =
VAR Description_product_catalog =
    LOOKUPVALUE (
        Product_Catalog[description];
        Product_Catalog[item]; Unique_Items[item]
    )
VAR Description_market =
    LOOKUPVALUE (
        Master_Catalog[description];
        Master_Catalog[item]; Unique_Items[item]
    )
RETURN
    IF (
        Description_product_catalog = "";
        Description_market;
        Description_product_catalog
    )

Should do expected result

Attach PBIX file.

Regards,

MFelix