Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi guys,
I have issue when using the SELECTEDVALUE.
If I hard-code year number ( 2020 ), the data of "PY Revenue" shows in all weeks. (first picture).
And it's vice versa, I replace 2020 value by " SELECTEDVALUE(DIM_DATE[fin_year])-1 ", the data just show to Week 5 (second picture).
I tried in another way, but I got and error. (3th picture)
Please help me to understand and how to fix this case.
Thank you very much
Solved! Go to Solution.
Hi @Hung_Nguyen_889 ,
It is hard to tell without checking your pbix file (consider sharing a sample if you still cannot sort this out) but, to rule out a possible cause, make sure the filter you have placed in your Filter Pane (fin_year = 2021) comes also from the DIM_DATE table.
I tried your formula with some dummy data and works fine when filtering as said before. See below:
Regards,
Hi @Hung_Nguyen_889 ,
As the error message describes, SELECTEDVALUE() could not be used as a T/F expression with a filter expression in the formula, you can use a variable to store it and use it in CALCULATE() function as @ Payeras_BI mentioned.
In addition, you can also add a FILTER() function in the formula to quote SELECTEDVALUE() directly which is allowed in DAX:
PY Revenue =
CALCULATE (
[Total Revenue $],
FILTER (
ALL ( 'DIM_DATE' ),
DIM_DATE[fin_year]
= SELECTEDVALUE ( DIM_DATE[fin_year] ) - 1
)
)
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Hung_Nguyen_889 ,
As the error message describes, SELECTEDVALUE() could not be used as a T/F expression with a filter expression in the formula, you can use a variable to store it and use it in CALCULATE() function as @ Payeras_BI mentioned.
In addition, you can also add a FILTER() function in the formula to quote SELECTEDVALUE() directly which is allowed in DAX:
PY Revenue =
CALCULATE (
[Total Revenue $],
FILTER (
ALL ( 'DIM_DATE' ),
DIM_DATE[fin_year]
= SELECTEDVALUE ( DIM_DATE[fin_year] ) - 1
)
)
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Hung_Nguyen_889 ,
It is hard to tell without checking your pbix file (consider sharing a sample if you still cannot sort this out) but, to rule out a possible cause, make sure the filter you have placed in your Filter Pane (fin_year = 2021) comes also from the DIM_DATE table.
I tried your formula with some dummy data and works fine when filtering as said before. See below:
Regards,
maybe SELECTEDVALUE get a blank on those rows.
Thank you for your response,
SELECTEDVALUE got 2020 value. It still showed data from week 1 to week 5. 😞
try to chante your measure
RETURN _Previous_Year
Hi @Hung_Nguyen_889 ,
Try with this instead:
PY REVENUE =
CALCULATE ( [Total Revenue $], SAMEPERIODLASTYEAR ( DIM_DATE[date] ) )
Regards,
Hi @Payeras_BI
Thank you for your response,
I can't use SAMEPERIODLASTYEAR function. Because, our financial date is different.
The fist date of 2020 is starting at '2019-12-29' and the ending date is '2021-01-02'
The fist date of 2021 is starting at '2021-01-03' and the ending date is '2022-01-01'
So, it is incorrect when I use SAMEPERIODLASTYEAR
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.