How to write measure for specific condition for slicer?

[YunJ]

Hi, 

I have a text column called "Year_bd", and I used it as slicer. Then I wrote a measure called GMV_LY, which means for example when Year_bd=2021 is selected, it will show sum(GMV) for 2020 (which is 2021-1).

But it shows cannot display the visual when I add it.

[Vera_33]

Hi @YunJ 

So your Year_bd is like 2021 but text? Convert it first? Or can you change the data type?

CONVERT(SELECTEDVALUE(Sheet1[year_bd]),INTEGER)

[YunJ]

Hi Vera,

I can have the correct result if I change it to "Whole number". But I'm wondering if there is any method that I can write DAX to accomplish this?

Please find the pbix here:

https://www.dropbox.com/s/om3z8h7qqdfb82q/SBD.pbix?dl=0

Thanks a lot for your time.

[Vera_33]

Hi @YunJ 

Have you tried to CONVERT it? The above DAX, I can't use dropbox...

[YunJ]

Hi @Vera_33 

I tried the CONVERT measure, but still fail.

[Vera_33 · Accepted solution]

Hi @YunJ 

I see the problem...no need to convert the above one, but the below one, you can use either way, convert or the way provided by @amitchandak 

GMV_LY = 

VAR a = SELECTEDVALUE(Sheet1[year_bd])-1
RETURN
CALCULATE(SUM(Sheet1[GMV],Sheet1[year_bd]*1=a)

OR...

GMV_LY = 

VAR a = SELECTEDVALUE(Sheet1[year_bd])-1
RETURN
CALCULATE(SUM(Sheet1[GMV],CONVERT(Sheet1[year_bd],INTEGER)=a)

[YunJ]

Thanks a lot @Vera_33 . It works!

So the function selectedvalue will return as number right? Even if sheet1[year_bd] is string?

SELECTEDVALUE(Sheet1[year_bd])-1

Another question is sheet1[year_bd]*1 will return as number right? even if sheet1[year_bd] is string?

Sheet1[year_bd]*1

Thanks again for your time!

[amitchandak]

@YunJ , Change var a like

var a = selectedvalue(Sheet1[year_bd])*1 -1

Otherwise, share the error.

[YunJ]

Hi @amitchandak 

I can have the correct result if I change it to "Whole number". But I'm wondering if there is any method that I can write DAX to accomplish this?

I tried your solution but fail to accomplish.

Please find the pbix here:

https://www.dropbox.com/s/om3z8h7qqdfb82q/SBD.pbix?dl=0

Thanks a lot for your time.