SWITCH function problem

[schoden]

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.

https://netlinkgroup-my.sharepoint.com/:u:/g/personal/schoden_netlinkgroup_com_au/EXs9DuXKukVPvzVPZD...

Thank you in advance.

[schoden]

@AllisonKennedy   Thank you so much for the solution. 

[AllisonKennedy · Accepted solution]

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-firstyear

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