Forum Discussion
Filtering a calculation group using a slicer with date (weekyear)
I apologize for the oversight. It seems there's a minor issue with your DAX formula. The error you're encountering is because it's having trouble determining a single value for 'WeekYear' in the context of your calculation.
To resolve this issue, you can slightly modify your DAX formula. You should use the VALUES function to retrieve the distinct weekyear values for the selected context. Here's the updated formula:
CALCULATE(
SELECTEDMEASURE(),
YEAR(CalendarTable[Date]) = YEAR(TODAY()) &&
WEEKNUM(CalendarTable[Date]) = WEEKNUM(TODAY()),
FILTER(ALL(CalendarTable), CalendarTable[WeekYear] = SELECTEDVALUE(WeekyearTable[Weekyear]))
)In this formula, we're using SELECTEDVALUE(WeekyearTable[Weekyear]) to retrieve the selected weekyear from your slicer. This should resolve the error and correctly filter your calculation based on the selected weekyear.
Make sure that you have a 'WeekyearTable' with the 'Weekyear' column connected to your slicer, and it contains distinct weekyear values corresponding to your 'CalendarTable'.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.
123abc thank you for your quick response, but I am getting the following error
Expression: A single value for column 'WeekYear' in table 'CalendarTable' cannot be determined
when using this formula
CALCULATE( SELECTEDMEASURE(), YEAR(CalendarTable[Date]) = YEAR(TODAY()) && WEEKNUM(CalendarTable[Date]) = WEEKNUM(TODAY()), FILTER(ALL(CalendarTable[Date]), CalendarTable[WeekYear] = SELECTEDVALUE([WeekNum])) )
WeekYear is a column in my CalendarTable
Community ChampionI apologize for the oversight. It seems there's a minor issue with your DAX formula. The error you're encountering is because it's having trouble determining a single value for 'WeekYear' in the context of your calculation.
To resolve this issue, you can slightly modify your DAX formula. You should use the VALUES function to retrieve the distinct weekyear values for the selected context. Here's the updated formula:
CALCULATE(
SELECTEDMEASURE(),
YEAR(CalendarTable[Date]) = YEAR(TODAY()) &&
WEEKNUM(CalendarTable[Date]) = WEEKNUM(TODAY()),
FILTER(ALL(CalendarTable), CalendarTable[WeekYear] = SELECTEDVALUE(WeekyearTable[Weekyear]))
)
In this formula, we're using SELECTEDVALUE(WeekyearTable[Weekyear]) to retrieve the selected weekyear from your slicer. This should resolve the error and correctly filter your calculation based on the selected weekyear.
Make sure that you have a 'WeekyearTable' with the 'Weekyear' column connected to your slicer, and it contains distinct weekyear values corresponding to your 'CalendarTable'.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.
123abc , THANK YOU!
I had to make a few adjustments to the formula you provided and change my CalendarTable a bit but now it works perfectly!
Thank you!