topic Re: DAX Function / Query to create a flag for products in DAX Commands and Tips

DAX Function / Query to create a flag for products

hassanh2 — Sat, 16 Jul 2022 09:57:14 GMT

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:

Based on a "Current Year" (Parameter / filter) the flag value for an Item/Product is:

- "New" - If product is sold in "Current Year" only

- "Existing" - If Product is sold in "Current Year" and in "Prior Year" (Current year -1)

- "No" - If Product was sold only in "Prior Year"

Thanks

Re: DAX Function / Query to create a flag for products

ribisht17 — Sat, 16 Jul 2022 18:16:18 GMT

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")

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

Re: DAX Function / Query to create a flag for products

daXtreme — Sun, 17 Jul 2022 09:01:21 GMT

hassanh2

If you say that you want to have a parameter for Current Year, then it has to be a measure out of necessity. Values in base tables cannot be changed once they've been refreshed. Also, if you want to stay sane and obtain a good model, you have to have at least 3 tables. One where you'll store your products, one which will be a Date table (Calendar) and one fact table storing the transactions. Without such a setup you'll be in trouble. But it's up to you, of course 🙂

Re: DAX Function / Query to create a flag for products

hassanh2 — Sun, 17 Jul 2022 19:43:25 GMT

You are absolutely right. I do have Date (Calendar) tabel which will be used to define Current year filter. I was just looking for the logic Item/Product Master and fact table.

Re: DAX Function / Query to create a flag for products

hassanh2 — Sun, 17 Jul 2022 20:03:22 GMT

Thank you Ritesh - this was helpfull and very close to what Im looking for, however the switch flag value in last row in your screen shot should be "New" since "Year" and "Latest Order Year" are both "2022".

Re: DAX Function / Query to create a flag for products

ribisht17 — Mon, 18 Jul 2022 01:59:16 GMT

That is Nokia which was there in the last year as well(2nd row),so it should be existing ,right ?

Re: DAX Function / Query to create a flag for products

ribisht17 — Mon, 18 Jul 2022 05:12:39 GMT

hassanh2

Did it help?

Please set the answer as Correct so that it can help others as well

Regards,

Ritesh

Re: DAX Function / Query to create a flag for products

hassanh2 — Mon, 18 Jul 2022 07:11:38 GMT

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"?

Re: DAX Function / Query to create a flag for products

ribisht17 — Mon, 18 Jul 2022 07:56:35 GMT

Mark my post as a solution if it helped you| Munde and Kudis (Ladies and Gentlemen) I like your Kudos!! !!
My YT Channel Dancing With Data !! Connect on Linkedin !!Power BI for Tableau Users