Count the order numbers of certain product combination

[JoyTsai]

Hiiii. I would like to calculate the number of orders which contain certain product combination.

Here's the model.
SalesOrder Table-Primary Key: Sales Order Line Key
Sales Table-Primary Key: Sales Order Line Key
A Product Table-Primary Key: Product Key. was created manually based on Product table.
Product Table-Primary Key: Product Key

Both A Product Table and Product Table relate with Sales Table by Product Key.
Sales Table is related with Sales Order table by Sales Order Line Key. One to one relationship.

I created this measure below and power bi didn't report a error on this measure itself--
Var FSTproduct= CALCULATETABLE(DISTINCT(SalesOrder[Sales Order]),CROSSFILTER('A Product Table'[Product Key],Sales[ProductKey],Both))

Var SNDproduct= CALCULATETABLE(DISTINCT(SalesOrder[Sales Order]),CROSSFILTER('Product'[ProductKey],Sales[ProductKey],Both))

RETURN IF(FSTproduct=SNDproduct,BLANK(),COUNTROWS(INTERSECT(FSTproduct,SNDproduct)))

When I wanna to use this measure in the matrix table, with Product Key from A Product Table on Rows filed, Product Key from Product table on Columns field and this measure on Values field, Power bi reports that “couldn't load the data for this visual because A table of multiple values was supplied where a single value was expected". 

Can anyone help to fix this measure? I have seen someone asking similar question in the forum but since I have a different model from that post, the solutions offered in that post don't work quite well for my issue. And I have been stuck in this issue for quite a few days. thanksss for the help!!

[mark_endicott · Accepted solution]

@JoyTsai - Ok I was definitely oversimplifying it! I think this solution should work for you. 

1) Change the distinct count measure to the one below, it will be more efficient for this type of analysis. 

Number of Orders = SUMX ( SUMMARIZE ( SalesOrder, SalesOrder[Sales Order] ), 1 )

2) Make the relationship of your 'A Product Table' inactive - we will activate it in the measures. 

3) Create a measure that you'll hide, and only use in the next steps:

Orders Both (Internal) =
VAR OrdersWithAndProducts =
    CALCULATETABLE (
        SUMMARIZE ( SalesOrder, SalesOrder[Sales Order] ),
        REMOVEFILTERS ( 'Product' ),
        REMOVEFILTERS ( Sales[ProductKey] ),
        USERELATIONSHIP ( Sales[ProductKey], 'A Product Table'[Product Key] )
    )
VAR Result =
    CALCULATE (
        [Number of Orders],
        KEEPFILTERS ( OrdersWithAndProducts )
    )
RETURN
    Result

4) Create a calculated table, that you will join to Product on ProductKey - This table can be hidden as it is only used to help the final measure. 

RawProductsOrders =
FILTER (
    SUMMARIZECOLUMNS (
        'Sales'[ProductKey],
        'A Product Table'[Product Key],
        "Orders", [Orders Both (Internal)]
    ),
    NOT ISBLANK ( [Orders] ) && 'A Product Table'[Product Key] <> 'Sales'[ProductKey]
)

5) Create the final measure:

Order Combinations :=
VAR ExistingAndProductKey =
    CALCULATETABLE (
        DISTINCT ( RawProductsOrders[Product Key] ),
        TREATAS (
            DISTINCT ( 'A Product Table'[Product Key] ),
            RawProductsOrders[Product Key]
        )
    )
VAR FilterAndProducts =
    TREATAS (
        EXCEPT (
            ExistingAndProductKey,
            DISTINCT ( 'Product'[ProductKey] )
        ),
        Sales[ProductKey]
    )
VAR OrdersWithAndProducts =
    CALCULATETABLE (
        SUMMARIZE ( SalesOrder, SalesOrder[Sales Order] ),
        REMOVEFILTERS ( 'Product' ),
        FilterAndProducts
    )
VAR Result =
    CALCULATE (
        [Number of Orders],
        KEEPFILTERS ( OrdersWithAndProducts )
    )
RETURN
    Result

Hopefully this technique works for you, I have amended it from a site called DAX Patterns, so it is not my own. I know of one more, less efficient way to do this, but you should try this more complex one first as it will have better performance. 

If this works, please accept as the solution for others with the same challenge. 

[JoyTsai]

Hello Mark! Unfortunately, it doesn't work. 

It only returns order counts of the same product key, which is not what I expect, like the screenshot shown below. 

I wanna to find the order numbers, for example, which purchase product key 212 and product key 217 in the same order. I tried to attach the PBIX file I am using but the post doesn't allow me to attach PBIX file or ZIP file.

[mark_endicott · Accepted solution]

@JoyTsai - Ok I was definitely oversimplifying it! I think this solution should work for you. 

1) Change the distinct count measure to the one below, it will be more efficient for this type of analysis. 

Number of Orders = SUMX ( SUMMARIZE ( SalesOrder, SalesOrder[Sales Order] ), 1 )

2) Make the relationship of your 'A Product Table' inactive - we will activate it in the measures. 

3) Create a measure that you'll hide, and only use in the next steps:

Orders Both (Internal) =
VAR OrdersWithAndProducts =
    CALCULATETABLE (
        SUMMARIZE ( SalesOrder, SalesOrder[Sales Order] ),
        REMOVEFILTERS ( 'Product' ),
        REMOVEFILTERS ( Sales[ProductKey] ),
        USERELATIONSHIP ( Sales[ProductKey], 'A Product Table'[Product Key] )
    )
VAR Result =
    CALCULATE (
        [Number of Orders],
        KEEPFILTERS ( OrdersWithAndProducts )
    )
RETURN
    Result

4) Create a calculated table, that you will join to Product on ProductKey - This table can be hidden as it is only used to help the final measure. 

RawProductsOrders =
FILTER (
    SUMMARIZECOLUMNS (
        'Sales'[ProductKey],
        'A Product Table'[Product Key],
        "Orders", [Orders Both (Internal)]
    ),
    NOT ISBLANK ( [Orders] ) && 'A Product Table'[Product Key] <> 'Sales'[ProductKey]
)

5) Create the final measure:

Order Combinations :=
VAR ExistingAndProductKey =
    CALCULATETABLE (
        DISTINCT ( RawProductsOrders[Product Key] ),
        TREATAS (
            DISTINCT ( 'A Product Table'[Product Key] ),
            RawProductsOrders[Product Key]
        )
    )
VAR FilterAndProducts =
    TREATAS (
        EXCEPT (
            ExistingAndProductKey,
            DISTINCT ( 'Product'[ProductKey] )
        ),
        Sales[ProductKey]
    )
VAR OrdersWithAndProducts =
    CALCULATETABLE (
        SUMMARIZE ( SalesOrder, SalesOrder[Sales Order] ),
        REMOVEFILTERS ( 'Product' ),
        FilterAndProducts
    )
VAR Result =
    CALCULATE (
        [Number of Orders],
        KEEPFILTERS ( OrdersWithAndProducts )
    )
RETURN
    Result

Hopefully this technique works for you, I have amended it from a site called DAX Patterns, so it is not my own. I know of one more, less efficient way to do this, but you should try this more complex one first as it will have better performance. 

If this works, please accept as the solution for others with the same challenge. 

[JoyTsai]

Hey @mark_endicott  Thanks for the detailed steps! 🤝 It's a really complicate set of measures.

I am still trying to figure out how this set of measures work together to come out the result. 

For now, I simply followed your instruction, copy paste the measures into my report. And it works in a unexpected way, like the screenshot shown below.

For left visual, I dragged order both (internal) measure into the values field, looks like it works;

For right visual, I dragged order combinations measure into the values filed and it doesn't work. But this measure should be the one that works right?

And one extra question: If I would like to remove any product combination with the same product key from the visual, like removing 214(row)&214(column), 289(row)&289(column)...

How the measures should be updated?

Thankss for your help! Really appreciated!

[mark_endicott · Accepted solution]

@JoyTsai - you may also wish to consider fitting this technique to your model:

https://medium.com/@cseprs_54978/effective-product-pairing-analysis-with-crossjoin-in-dax-dd7e07c6c7...

Just think of the basket table as your 2nd product table.

Hopefully this helps!

[JoyTsai]

Hey @mark_endicott Thanks for the help!

I tried the method in the article you shared and also double checked the previous solution you offered and found that both of them work! 

I narrow down the product combination analyzed as you suggested in the visual and both solutions come out the same result.👇

However, I made a slight adjustment based on the solution offered in that article: I created a measure instead of a column.

I personally will recommend new users like me to take the second approach mainly because it creates less new measures/columns and easier to understand. The first approach may have better performance but it's too complicate for me to understand. 

Plus, I am still wondering why we need a product combination measure when orders both (internal) measure actually works. 😕

[mark_endicott]

@JoyTsai - This is really complex analysis and it is not suprising that it has not worked over your entire sales table. 

I would suggest that you use some categorical, or date filters in either slicers, or on the filter pane, to narrow down the numbers of products you are analysing. If you are using the filter pane you can filter at the page, visual or workbook level, a slicer will do this at the page level. 

If this does not work, then I would suggest looking at another way of visualising this, using CONCATENATEX over the product key in your sales table to concatenate the combinations. 

Please consider accepting this as the solution, as I have helped with your DAX challenge. 

[mark_endicott]

@JoyTsai - Forgive me if I'm over simplifying this, but without some sample data it's difficult to test. 

As far as I can see you should just need a measure to count the Sales Orders

DISTINCTCOUNT(SalesOrder[Sales Order])

And then in the Matrix visual you just need to add the two columns for the two tables and use the measure above. 

The relationships should handle the rest. 

If this works, please accept as the solution, if it does not please share some sample data for further testing.