Need help with DAX

Hi Folks, I have two tables, SALES and ITEMS. I have also created a duplicate of ITEMS table called 'FILTERING ITEM' I need the result table to look like : The ...

stevedep

6 years ago

In that case you can apply the following code:

Unique Orders = 
VAR ItemCount = VALUES(Sales[ORDER_ID])
VAR FilterItemCount = CALCULATETABLE(VALUES(Sales[ORDER_ID]);ALL(Sales);TREATAS(VALUES('Filtering Item'[ITEM_ID]); Sales[ITEM_ID]))

RETURN
if(VALUE(MIN(Items[ITEM_ID]))<=VALUE(MIN('Filtering Item'[ITEM_ID]));
COUNTROWS(INTERSECT(ItemCount;FilterItemCount));BLANK())

as seen here:

Link to file here.

Hope this helps you Anonymous .

Kind regards, Steve.

stevedep

Memorable Member

6 years ago

Hi,

I guess you are trying to implement:

https://www.daxpatterns.com/basket-analysis/

Which in your case would be:

Orders with Both Products = 
CALCULATE (
    DISTINCTCOUNT (Sales[ORDER_ID] );
    CALCULATETABLE (
        SUMMARIZE ( Sales;Sales[ORDER_ID] );
        ALL ( Items );
        USERELATIONSHIP ( Sales[ITEM_ID]; 'Filtering Item'[ITEM_ID] )
    )
)

Please mind the data model:

Result is as expected, as can be seen here:

Power BI file is available here.

Hope it helps, if so, please mark as solution. Thums up for the effort is appreciated.

Kind regards, Steve.

stevedep

Memorable Member

6 years ago

In order to understand what is happening I broke down the calculation, we essentially see that the;

CALCULATE (
    DISTINCTCOUNT (Sales[ORDER_ID] );

In itself is counting the orders which remain after applying only the filter context set by the columns, the items table.

The calculated table itself is getting the orders with the 'filtered item' only, in this case the items in the rows.

And this table later is used as a filter on the first count (of orders filtered by the column).

In the screen below I broke it down into pieces:

Formula for the top left:

Measure = 
var __cttbl =  CALCULATETABLE (
        SUMMARIZE ( Sales;Sales[ORDER_ID] );
        ALL ( Items );
        USERELATIONSHIP ( Sales[ITEM_ID]; 'Filtering Item'[ITEM_ID] ))

return
CONCATENATEX(__cttbl;" oid: " & [ORDER_ID])

Formula for the bottom left:

Measure2 = 
var __cttbl =  CALCULATETABLE (
        SUMMARIZE ( Sales;Sales[ORDER_ID] );
        ALL ( Items );
        USERELATIONSHIP ( Sales[ITEM_ID]; 'Filtering Item'[ITEM_ID] ))

return
CALCULATE(CONCATENATEX(Sales; "oid: " & [ORDER_ID]))

Formula to the bottom right:

Measure3 = 
var __cttbl =  CALCULATETABLE (
        SUMMARIZE ( Sales;Sales[ORDER_ID] );
        ALL ( Items );
        USERELATIONSHIP ( Sales[ITEM_ID]; 'Filtering Item'[ITEM_ID] ))

return
CALCULATE(CONCATENATEX(Sales; "oid: " & [ORDER_ID]); __cttbl)

I find it very helpful to use variables and concatenatex to understand what is going on and/or debug code.

Hope it helps people out there. Thumbs up if it does.

Forum Discussion

Need help with DAX