Forum Discussion
Creating a dynamic filtered table variable
Hi CeeJayC,
Thank you for your patience . The issue is that SWITCH and IF in DAX only return scalar values, not tables, which caused the parameter is not the correct type error in your earlier attempts.To resolve this, you need to adjust the logic to switch between complete calculations, such as the results from SUMX, rather than switching between tables.
SWITCH works in this context because it produces scalar values from each SUMX calculation. Each branch uses its own FILTER, so the slicer selection still manages the filtering logic. This approach avoids both the type mismatch error and the “multiple columns cannot be converted to scalar” issue.
Please give it a try and let us know how it goes.
Regards,
Community Support Team.
Super UserHi CeeJayC ,
Could ypu please try with below code
Filtered Table Test(v1) =
VAR Comparison_Basis = SELECTEDVALUE('Comparison Basis'[Comparison])
VAR BaseTable = ALL(Data)
VAR Filtered_Table =
SWITCH(TRUE(),
Comparison_Basis = "Month v FCST 1", FILTER(BaseTable, Data[Volume (FCST1)] <> 0),
Comparison_Basis = "Month v FCST 2", FILTER(BaseTable, Data[Volume (FCST2)] <> 0),
FILTER(BaseTable, TRUE)
)
RETURN
SUMX(Filtered_Table, Data[Volume (ACT)] - Data[Volume (FCST1)])
Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.
Hello ajaybabuinturi
Unfortunately, this did not resolve the issue. I am still getting the "parameter is not the correct type" error and an error indicating that "the expression refers to multiple columns. Multiple columns cannot be converted to a scalar value".- amitchandak
CeeJayC , Based on what I have used in the past, it is better to switch measure calculations in such cases example
Filtered Table Test(v1) =
VAR Comparison_Basis = SELECTEDVALUE('Comparison Basis'[Comparison])RETURN
SWITCH(TRUE(),
Comparison_Basis = "Month v FCST 1", SUMX(Filter(Data, Data[Volume (FCST1)] <> 0),Data[Volume (ACT)] - Data[Volume (FCST1)])
Comparison_Basis = "Month v FCST 2", SUMX(Filter(Data,Data[Volume (FCST2)] <> 0),Data[Volume (ACT)] - Data[Volume (FCST2)])
)
