Forum Discussion
Solved
SWITCH function problem
Hi Community,
I want to group customer based on years of transaction using SWITCH function.
For example customer A first transaction in 2010 , last transaction in 2020 , so years of transaction is 10.
Like wise for other customers.
Date Diff(measure)
MAX('Table'[Year ])-MIN('Table'[Year ])
Loyalty(New Column)
Loyalty = SWITCH(TRUE(),
[DateDiff]>=0 && [DateDiff]<=2, "BRONZE",
[DateDiff]>=3 && [DateDiff]<=5, "SILVER",
[DateDiff]>=6 && [DateDiff]<=8, "GOLD")
But the column Loyalty when put in a slicer, it doesnt show all groups.
Please check the sample data.
Thank you in advance.
You need to give it a row context to evaluate the DateDiff before you can do the switch, try doing the datediff as a column in the table:
Years as Customer =var customer = 'Table'[Customer ]var firstyear = MINX(FILTER(ALL('Table'),'Table'[Customer ] = customer), 'Table'[Year ])var lastyear = MAXX(FILTER(ALL('Table'),'Table'[Customer ] = customer), 'Table'[Year ])RETURN lastyear-firstyearLoyalty = SWITCH(TRUE(),[Years as Customer]>=0 && [Years as Customer]<=2, "BRONZE",[Years as Customer]>=3 && [Years as Customer]<=5, "SILVER",[Years as Customer]>=6 && [Years as Customer]<=8, "GOLD")
2 Replies
You need to give it a row context to evaluate the DateDiff before you can do the switch, try doing the datediff as a column in the table:
Years as Customer =var customer = 'Table'[Customer ]var firstyear = MINX(FILTER(ALL('Table'),'Table'[Customer ] = customer), 'Table'[Year ])var lastyear = MAXX(FILTER(ALL('Table'),'Table'[Customer ] = customer), 'Table'[Year ])RETURN lastyear-firstyearLoyalty = SWITCH(TRUE(),[Years as Customer]>=0 && [Years as Customer]<=2, "BRONZE",[Years as Customer]>=3 && [Years as Customer]<=5, "SILVER",[Years as Customer]>=6 && [Years as Customer]<=8, "GOLD")
