Search code examples
daxtabular

DAX - Advanced Product Grouping/Segmentation Question

I created an SSAS Tabular model using the AdventureWorksDW database.

I used the post below to help me build the report.

https://blog.gbrueckl.at/2014/02/applied-basket-analysis-in-power-pivot-using-dax/

Sold in same Order:=
CALCULATE (
COUNTROWS ( 'Internet Sales' ),
CALCULATETABLE ( 
SUMMARIZE ( 
'Internet Sales',
'Internet Sales'[SalesOrderNumber]
),
ALL ( 'Product' ) ,
USERELATIONSHIP( 'Internet Sales'[ProductKey],FilteredProduct[ProductKey])
)
)

I have validated that the results from the formula are correct. There are 1,381 orders with the Touring Tire Tube sold and shows me how many orders were sold with the other items (e.g. 170 out of the 1,381 orders also included product key 214 - Sport-100 Helmet, Red).

enter image description here Here is where I'm having an issue. I would like to summarize my data by showing how many of the orders only included my filtered items vs. orders sold with other products. This has to be dynamic since users can select any products... The end result should look like this:

enter image description here

I'm new to DAX and have struggled with this for a few hours. Thanks for your help.

Here is the table relationship:

enter image description here

Solution

  • this DAX should work on the example dataset from my blog:

    Orders with only the filtered products = 
--VAR vFilteredProducts = VALUES('Filtered Product'[ProductKey])
VAR vFilteredProducts = FILTER('Filtered Product', [ProductKey] = 530 || [ProductKey] = 541)
VAR vCountFilteredProducts = COUNTROWS(vFilteredProducts)
VAR vSales = CALCULATETABLE('Internet Sales',  -- get the Sales for the filtered Products
    vFilteredProducts,
    USERELATIONSHIP('Filtered Product'[ProductKey], 'Internet Sales'[ProductKey]), 
    ALL('Product'))
VAR vOrders = SUMMARIZE( -- Summarize the filtered product sales by Sales Order Number
    vSales, 
    [Sales Order Number],
    -- calucate the distinct filtered products in the filtered orders
    "CountFilteredProductsInOrder", CALCULATE(DISTINCTCOUNT('Internet Sales'[ProductKey])),
    -- calculate the all distinct products for the filtered orders 
    "CountTotalProductsInOrder", CALCULATE(DISTINCTCOUNT('Internet Sales'[ProductKey]), 
                ALLEXCEPT('Internet Sales', 'Internet Sales'[Sales Order Number]))
    )
RETURN COUNTROWS(
    FILTER(
        vOrders, 
        -- the total product count has to match the filtered product count --> no other products except filtered ones in order
        [CountFilteredProductsInOrder] = [CountTotalProductsInOrder]
    )
)

    To get the orders where also other products except the filtered ones were sold, imply change the last FILTER() from '=' to '<'