Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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.
Solved! Go to 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)
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?
Please find the pbix here:
https://www.dropbox.com/s/om3z8h7qqdfb82q/SBD.pbix?dl=0
Thanks a lot for your time.
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!
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
126 | |
78 | |
78 | |
59 | |
51 |
User | Count |
---|---|
165 | |
83 | |
68 | |
68 | |
59 |