Forum Discussion

dborrelli's avatar
dborrelli
Regular Visitor
8 years ago

Dynamic column based on slicer selection

Hi all,

 

I work in higher ed and I want my end users to be able to compare the application details form any term to the previous application cycle for that term (e.g. Fall 2016 -> Fall 2015, Spring 2017 -> Spring 2016). I've created a table ('Terms') which identifies which term is "Current" and which is "Previous" based on the user's selection of a slicer connect to my main FactTable.

 

I've successfully identified what is "Current" based on the user's slicer selection. Look at column "Designation2" in the below image and you'll see that 2017 Fall is designated as the current year:

 

 

What I can't seem to get right is for "Designation2" to simultaneously identify - in this example - 2016 Fall as the previous year. here is the DAX I'm using; if anyone has a minute, I'd really appreciate some insight into where I've gone wrong.

 

Designation2 =

     IF(ISFILTERED ('FactTable'[Entry Term]) && HASONEVALUE ('FactTable'[Entry Term]) && MIN('Terms'[Entry Term]) = MIN('FactTable'[Entry Term]), "Current Year",

     IF(ISFILTERED ('FactTable'[Entry Term]) && HASONEVALUE ('FactTable'[Entry Term]) && REPLACE(MIN(Terms[Entry Term]),1,5,"") = REPLACE(MIN('FactTable'[Entry Term]),1,5,"") && VALUE(LEFT(MIN(Terms[Entry Term]),4)) = (VALUE(LEFT(MIN('FactTable'[Entry Term]),4))-1), "Previous Year", 

     MIN('Terms'[Entry Term])))

 

2 Replies