Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
YunJ
Post Prodigy
Post Prodigy

How to write measure for specific condition for slicer?

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.

1.png2.png

1 ACCEPTED SOLUTION
Vera_33
Resident Rockstar
Resident Rockstar

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)

 

View solution in original post

8 REPLIES 8
Vera_33
Resident Rockstar
Resident Rockstar

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)

  

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?

1.png

Please find the pbix here:

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

Thanks a lot for your time.

Vera_33
Resident Rockstar
Resident Rockstar

Hi @YunJ 

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

Hi @Vera_33 

I tried the CONVERT measure, but still fail.

1.png

Vera_33
Resident Rockstar
Resident Rockstar

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)

 

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
Super User
Super User

@YunJ , Change var a like

 

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

 

Otherwise, share the error.

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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?

1.png

I tried your solution but fail to accomplish.

2.png

Please find the pbix here:

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

Thanks a lot for your time.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.