Forum Discussion

schoden's avatar
schoden
Post Partisan
5 years ago
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...
Need Help
  • AllisonKennedy's avatar
    AllisonKennedy
    5 years ago

    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")
AllisonKennedy's avatar
AllisonKennedy
Community Champion
5 years ago

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