cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NikitaDalela
Helper II
Helper II

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'

SALES.JPG

ITEMS.JPG

 

 

 

 

 

I need the result table to look like :

Desired result.JPG

 

The DAX I'm using now is like

Unique Orders =
VAR ItemCount = CALCULATETABLE(VALUES(Sales[ORDER_ID]))
VAR FilterItemCount = CALCULATETABLE(VALUES(Sales[ORDER_ID]), TREATAS(VALUES(Sales[ITEM_ID]), 'Filtering Item'[ITEM_ID]))
RETURN
COUNTROWS(INTERSECT(ItemCount,FilterItemCount))

 

Can someone help me with this please?

 

 

Thanks,

Nikita

1 ACCEPTED SOLUTION

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:
uio.jpg

Link to file here

Hope this helps you @NikitaDalela .

Kind regards, Steve. 

View solution in original post

11 REPLIES 11
stevedep
Super User I
Super User I

As for the answer to your question, the code should be:

 

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
COUNTROWS(INTERSECT(ItemCount;FilterItemCount))

 

 as seen here:

sh.jpg

 

With var FilterItemCount we first take all sales data and then apply the filter on item, set by the independent item table using treatas. 

 

File is here

Pls mark as solution if this works for you.

Kind regards, Steve. 

stevedep
Super User I
Super User I

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:

dm2.jpg

Result is as expected, as can be seen here:

result5.jpg

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. 

Hi @stevedep ,

Thanks for such a detailed explanation. This is what I have implemented. I am getting the below result as of now.

RESult1.JPG

However, I wish to see the data in a correlation heatmap like below. (https://www.linkedin.com/pulse/basket-analysis-power-bi-jani-zajc/)

DesiredResult.JPG

Do you have an idea how to achieve this?

 

 

Thanks,

Nikita

Enable conditional formatting in the matrix on the measure.

 

Glad to be of help.

Hi @stevedep ,

I am aware about the conditional formatting, however it is not the conditional formatting that I was talking about.

In the correlation heatmap below, there is no data in the intersection of same categories, for example, (Category2, Category2). Data can be seen only for different category combinations. Also, combinations are not repeating, for example (category2,category3) has some data while (category3,category2) is blank.

 DesiredResult.JPG 

In my result, i have data in combinations like (beef,beef), (soda,soda) and also in repetitive combinations, (beef,onions), (onions,beef) and so on.

RESult1.JPG

 

 

Thanks,

Nikita

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:
uio.jpg

Link to file here

Hope this helps you @NikitaDalela .

Kind regards, Steve. 

View solution in original post

Hi @stevedep ,

This is working exactly as required. Thanks a lot to put in all the effort!🙂

 

 

Thanks,

Nikita

Hi, 

see below. condformatting.jpg

Please mark as solution if this works for you. Many thanks!

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:

fi.jpg

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. 

harshnathani
Super User III
Super User III

Hi @NikitaDalela ,

 

Cannot understand the logic of your output.

 

Can you pls explain the output.

 

Regards,

Harsh Nathani

Hi @harshnathani ,

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] )
)

 

 

 

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors