Forum Discussion
Cannot Reference Field in Table for Filter in CALCULATE
Hi all, I've written the below DAX to calculate a list of all KPIs showing only the 3rd best branch's result. I've provided the full code for reference however, I've pulled the troublesome part of...
Hello Kaycee ,
I believe the issue may arise from the context you are writing the variable _Department within
To calarify, variables such as _KFA, _PeriodType, and _Department are often defined within functions like ADDCOLUMNS which uses Rowcontext.
However, when CALCULATE is used, it transitions to filter context. This filter context does not automatically recognize variables defined in the outer row context, such as _Department. This can cause issues when trying to reference these variables within CALCULATE.
Please try this snippet. I hope it helps.
VAR _TableOfKFAResults =
ADDCOLUMNS (
_ListAllKFAPeriodBranchCombinations,
"PodiumResult",
VAR _KFA = [Key Focus Area (KFA)]
VAR _PeriodType = [Period Type]
VAR _Department = [Department]
VAR _SelectedDepartment =
SELECTCOLUMNS(
FILTER(
_ListOfDepartments,
[Department with Branch] = _Department
),
[Original Department]
)
VAR _PodiumResult =
CALCULATE(
SWITCH(
_KFA & "_" & _PeriodType,
"Total Revenue_MTH", [Total Revenue MTH Act],
"Total Revenue_FYTD", [Total Revenue FYTD Act]
),
Master_Accounts[Department (Summary)] IN _SelectedDepartment
)
RETURN _PodiumResult
)