Creating a dynamic filtered table variable

[CeeJayC]

Hello! I'm hoping there is a simple DAX misunderstanding on my part here. My underlying sales data ("Data") has two forecast bases, named FCST1 and FCST2. I want my users to be able to use a slicer on 'Comparison Basis'[Comparison] to pick which version of the forecast they are looking at. Depending on which one they choose, I need a different set of filters to be applied to the underyling data table for some SUMX calculations. In the code below, i've simplified the equations drastically in order to get to the underlying problem, which is that anytime I use a SWITCH or an IF statement to create different filtered tables, the value that variable returns is always "Not the correct type". If I just replace the "Filtered_Table" variable in the SUMX with either of the FILTER(Data,....) formulas, it works no problem. It also works fine if I remove the "SWITCH" portion of the Variable definition and just let Filtered_Table = FILTER(Data,....). But as soon as I add the SWITCH or IF in it gives me the error. Any idea on how to fix this or otherwise get a dynamically filtered table that can change based on a user's selection?

[CeeJayC]

Hello @CPCARDOSO 

Thank you for your response! Unfortunately, none of these 3 solutions solved my problem:

IF solution: I ran into the same issue, with the "Parameter is not the correct type" error. 

Switch solution: Perhaps I am misunderstanding, but from what I can see the DAX you entered there is exactly what I had submitted that resulted in an error.

Column as Variable solution: Again, this could be a misunderstanding on my part, but Power BI will not allow me to identify a solitary column as a variable to reference later in this way. It wants me to aggregate the column or else it results in an error. This doesn't work because the way I need to reference the column later is in the "Filter" formula and not as an aggregation.

[v-hjannapu · Accepted solution]

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.

[v-hjannapu]

Hi @CeeJayC,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We are always here to support you.


Regards,
Community Support Team.

[amitchandak]

@CeeJayC , You can not switch tables using if, which means filter in this case, which is returned by if 
https://www.reddit.com/r/PowerBI/comments/16zm64g/does_if_work_with_table_variables/

That is why I was suggesting to return the measure value/scaler value 

[Natalie_iTalent]

Hello @CeeJayC,

The response you are referring to above has been removed for inaccurate information.

Best,
Natalie H.

Community Manager 

[ajaybabuinturi]

Hi @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.

[CeeJayC]

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)])
)