Forum Discussion
hassanh2
Helper I
Helper IDAX 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: ...
Step1
Latest Order Year = CALCULATE(MAX(Sales[Year]),ALLEXCEPT(Sales,Sales[Product Name]))Step2Count 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
daXtreme
Solution Sage
Solution Sage
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 🙂
hassanh2Helper I
Helper IYou 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.