topic Re: Cannot Reference Field in Table for Filter in CALCULATE in DAX Commands and Tips

Cannot Reference Field in Table for Filter in CALCULATE

Kaycee — Fri, 14 Jun 2024 09:54:44 GMT

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 the code out below.

It all works fine except one spot - the virtual table in the variable _TableOfKFAResults. The error I receive is unhelpful ("An unexpected Exception Occured"). I've narrowed it down to the reference to the filter in the CALCULATE function.

In a nutshell, the variables (ie. in green) can be referenced in the Expression; however the variables cannot be used in the filter segment of CALCULATE (Red). Basically, if the _Department variable was hard coded, this works perfectly.

Advice would be appreciated.

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] )
RETURN
CALCULATE(
   SWITCH(
      _KFA & "_" & _PeriodType,
      "Total Revenue_MTH", [Total Revenue MTH Act],
      "Total Revenue_FYTD", [Total Revenue FYTD Act],
   ),
   Master_Accounts[Department (Summary)] IN _SelectedDepartment
)
)

EVALUATE // ####################### Identify Reporting Periods (including the last and current financial year to date) ####################### \\ VAR _Today = TODAY() VAR _CurrentYear = FORMAT(YEAR(_Today), "####") VAR _LastYear = FORMAT(YEAR(_Today) - 1, "####") // ####################### Create a table with all periods ####################### \\ VAR _ListOfPeriods = SUMMARIZE( FILTER( ALL( '_Calendar (Filters)'[Date], '_Calendar (Filters)'[Filter Date (MMM-YY)], '_Calendar (Filters)'[Filter Fin Year (YYYY)]), '_Calendar (Filters)'[Date] <= _Today && '_Calendar (Filters)'[Filter Fin Year (YYYY)] IN {_CurrentYear, _LastYear} ), '_Calendar (Filters)'[Filter Date (MMM-YY)] ) // ####################### Create a table with all branches (excludes Group) ####################### \\ VAR _ListOfBranches = CALCULATETABLE ( VALUES (Master_Branches[Branch]), Master_Branches[Region] <> "Group", NOT ISBLANK(Master_Branches[Branch]) ) VAR _ListOfDepartments = UNION( SELECTCOLUMNS( FILTER( VALUES(Master_Accounts[Department (Summary)]), Master_Accounts[Department (Summary)] IN {"Wholegoods", "Parts", "Service"}), "Original Department", [Department (Summary)], "Department with Branch", [Department (Summary)] ), SELECTCOLUMNS( VALUES(Master_Accounts[Department (Summary)]), "Original Department", [Department (Summary)], "Department with Branch", "Branch" ) ) // ####################### Create Table of All KFA - Month - Branch Combinations ####################### \\ VAR _ListAllKFAPeriodBranchCombinations = CROSSJOIN( VALUES(KFA_Listing[Key Focus Area (KFA)]), _ListOfPeriods, _ListOfBranches, SELECTCOLUMNS( _ListOfDepartments, "Department", [Department with Branch] ), SELECTCOLUMNS( {"MTH", "FYTD"}, "Period Type", [Value] ) ) // ####################### Calculate Whole Of Business KFA Results Table ####################### \\ 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] ) RETURN CALCULATE( SWITCH( ---------------- MONTH ---------------- _KFA & "_" & _PeriodType, "Total Revenue_MTH", [Total Revenue MTH Act], ---------------- YEAR TO DATE ---------------- "Total Revenue_FYTD", [Total Revenue FYTD Act], ), Master_Accounts[Department (Summary)] IN _SelectedDepartment ) ) // ####################### RANK THE KFAS, LIMIT TO 3RD BEST, SELECT ONLY RELEVANT COLUMNS ####################### \\ VAR _RankedResults = SELECTCOLUMNS( FILTER( ADDCOLUMNS ( _TableOfKFAResults, "Rank", VAR _CurrentKFA = [Key Focus Area (KFA)] VAR _CurrentPeriod = [Filter Date (MMM-YY)] VAR _CurrentPeriodType = [Period Type] VAR _CurrentDepartment = [Department] RETURN RANKX ( FILTER( _TableOfKFAResults, [Key Focus Area (KFA)] = _CurrentKFA && [Filter Date (MMM-YY)] = _CurrentPeriod && [Period Type] = _CurrentPeriodType && [Department] = _CurrentDepartment ), [PodiumResult], , DESC ) ), [Rank] = 3 ), [Key Focus Area (KFA)], '_Calendar (Filters)'[Filter Date (MMM-YY)], Master_Branches[Branch], [Department], [Period Type], [PodiumResult] ) RETURN _RankedResults

Re: Cannot Reference Field in Table for Filter in CALCULATE

MattAllington — Sat, 15 Jun 2024 22:07:04 GMT

I've been teaching DAX for 10 years now. I realised very early on that there are two types of people that learn DAX: there are people that come from an Excel background and people that come from a SQL server background. You are clearly the latter.

Each of these two groups make different mistakes. Excel users always try to solve problems with calculated columns. SQL users always try to solve problems by writing queries. Both of these approaches are wrong.

you need to use this tool differently to SQL server. You need to stop writing the query using DAX like a scripting language and start to use it as a UI driven, model based tool.

go to a blank report page, add a matrix (or table) to the canvas and start to build the structure of the output you want to see.
add columns from your model into the visual
write simply measures that help you on your journey to solve the problem. Add them to the visual so you can see the results.
continue to write measures referencing the earlier measures until the problem is solved

above all, do not do anything that involves the EVALUATE statement.

Re: Cannot Reference Field in Table for Filter in CALCULATE

Kaycee — Sun, 16 Jun 2024 09:35:07 GMT

Hi MattAllington ,

Thanks for taking the time to reply. I'm afraid you are incorrect - I was an accountant by trade but after a decade of intensive learning in BI, I switch between a range of options for solving challenges that best suit the situation. I'm guessing my approach in this instance is a reflection of the many people I've learnt from over the years.

With respect to your approach, I can see your point however I'd appreciate your insight on two aspects of your recommendation:
1) How do you then efficiently transition your solution back to a calculated table after using the UI interface when you consider a measure to not be the optimal outcome?

2) What about the learning aspect? I can find another way to write this that would avoid the error - however I chose to put it in the forum anyway, as the result isn't as I would expect and I've been unable to resolve why the error is happening. The understanding is of greater importance to me than the resolution of the code itself.

Thanks again
Kaycee

Re: Cannot Reference Field in Table for Filter in CALCULATE

Moetazzahran — Sun, 16 Jun 2024 10:54:35 GMT

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

)