Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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's avatar
    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. 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous ,

 

Cannot understand the logic of your output.

 

Can you pls explain the output.

 

Regards,

Harsh Nathani

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

    I am trying to implement market basket analysis. ITEMS table and FILTERING ITEM table are serving as my baskets here.

    I want the count of orders who purchased respective items from both baskets in matrix.

    I am getting the correct result when I use below DAX.

     

    BothItemsPurchased =
    CALCULATE (
    DISTINCTCOUNT( Sales[ORDER_ID] ),
    CALCULATETABLE (
    SUMMARIZE ( Sales,Sales[ORDER_ID] ),
    ALL ( Items ),
    USERELATIONSHIP ( Sales[ITEM_ID], 'Filtering Item'[ITEM_ID] )
    )