topic Need help with DAX in DAX Commands and Tips

Need help with DAX

Anonymous — Wed, 10 Jun 2020 05:23:11 GMT

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

Re: Need help with DAX

Anonymous — Wed, 10 Jun 2020 05:54:38 GMT

Hi Anonymous ,

Cannot understand the logic of your output.

Can you pls explain the output.

Regards,

Harsh Nathani

Re: Need help with DAX

Anonymous — Wed, 10 Jun 2020 06:31:56 GMT

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

)

Re: Need help with DAX

stevedep — Wed, 10 Jun 2020 06:41:23 GMT

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.

Re: Need help with DAX

stevedep — Wed, 10 Jun 2020 07:17:30 GMT

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.

Re: Need help with DAX

stevedep — Wed, 10 Jun 2020 09:23:51 GMT

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:

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.

Re: Need help with DAX

Anonymous — Wed, 10 Jun 2020 12:58:33 GMT

Hi stevedep ,

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

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

Do you have an idea how to achieve this?

Thanks,

Nikita

Re: Need help with DAX

stevedep — Wed, 10 Jun 2020 15:06:10 GMT

Enable conditional formatting in the matrix on the measure.

Glad to be of help.

Re: Need help with DAX

stevedep — Thu, 11 Jun 2020 06:21:03 GMT

Hi,

see below.

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

Re: Need help with DAX

Anonymous — Thu, 11 Jun 2020 07:14:48 GMT

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.

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.

Thanks,

Nikita

Re: Need help with DAX

stevedep — Fri, 12 Jun 2020 06:36:15 GMT

In that case you can apply the following code:

as seen here:

Link to file here.

Hope this helps you Anonymous .

Kind regards, Steve.

Re: Need help with DAX

Anonymous — Fri, 12 Jun 2020 08:26:43 GMT

Hi stevedep ,

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

Thanks,

Nikita