Forum Discussion

Anonymous's avatar
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's avatar
    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. 

stevedep's avatar
stevedep
Icon for Memorable Member rankMemorable 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's avatar
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