Forum Discussion

hassanh2's avatar
hassanh2
Icon for Helper I rankHelper I
4 years ago
Solved

DAX Function / Query to create a flag for products

Hello,   Can anyone help with the following: I have 2 tables (Item Master and Sales) and want to create a column/measure in the sales table to flag the products according to the following logic: ...
  • ribisht17's avatar
    ribisht17
    4 years ago

    hassanh2 

     

    Step1

    Latest Order Year = CALCULATE(MAX(Sales[Year]),ALLEXCEPT(Sales,Sales[Product Name]))
     
    Step2
    Count of Orders = CALCULATE(DISTINCTCOUNT(Sales[Year]),ALLEXCEPT(Sales,Sales[Product Name]),Sales[Year]>=year(TODAY())-1)
     
    Switch Flag = SWITCH( TRUE(), [Count of Orders]=1&&[Latest Order Year]=year(TODAY()),"New" ,[Count of Orders]=1,"No",[Count of Orders]>1&&[Latest Order Year]=year(TODAY()),"Existing")

     

    OR

    Using Variable,

     

    Switch Flag Parameter = var Latest_Order_Year = CALCULATE(MAX(Sales[Year]),ALLEXCEPT(Sales,Sales[Product Name]))

    var Count_of_Orders = CALCULATE(DISTINCTCOUNT(Sales[Year]),ALLEXCEPT(Sales,Sales[Product Name]),Sales[Year]>=year(TODAY())-1)
     
    return SWITCH( TRUE(), [Count of Orders]=1&&[Latest Order Year]=year(TODAY()),"New" ,[Count of Orders]=1,"No",[Count of Orders]>1&&[Latest Order Year]=year(TODAY()),"Existing")

     

    Regards,

    Ritesh

hassanh2's avatar
hassanh2
Icon for Helper I rankHelper I
4 years ago

That make sense! 

One more question I have here, is it possible apply this logic by adding additional dimention(s). In other words the flag should work for example on "Product" and "Region"?