Dynamic column based on slicer selection

[dborrelli]

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

[tringuyenminh92]

Hi @dborrelli,

The idea for dynamic changing dims is a proper relationships between Fact tabel - Dims and Filter options table with a little bit redundant data in fact or in consolidated dims table. I think you could refer my topic for dynamic columns on slicer selection. 

If this works for you please accept it as solution and also like to give KUDOS.

Best regards
Tri Nguyen

[v-shex-msft]

Hi @dborrelli,

Power bi not support to create dynamic column/table based on slicer chosen, please take a look at below article to know more about this.

Calculated Column/Table Change Dynamically According to Slicer Selection in the Report.

Regards,

Xiaoxin Sheng