Forum Discussion

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

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

Not applicable

6 years ago

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

Memorable Member

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
Not applicable
6 years ago
Hi stevedep ,
This is working exactly as required. Thanks a lot to put in all the effort!🙂

Thanks,
Nikita