Forum Discussion
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 ...
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.
stevedep
Memorable Member
Memorable MemberAs 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.