Forum Discussion
Need help with DAX
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.
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
Memorable MemberEnable conditional formatting in the matrix on the measure.
Glad to be of help.
- stevedep
Hi,
see below.
Please mark as solution if this works for you. Many thanks!
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
- stevedep
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.
Hi stevedep ,
This is working exactly as required. Thanks a lot to put in all the effort!🙂
Thanks,
Nikita