Using a field-selection parameter in functions

[ant_lah]

Hello,

I am using a fields selection parameter to choose between a few different columns. This works well when I'm feeding the selected column into a visual; however, when I try to feed it into a function, say,

Measure = SUM(Par_farms_select_1[Par_farms_select_1])

I get something along the lines of "the function SUM cannot work with values of type String".

My best current solution is a switch statement based on SELECTEDVALUE, as follows:

Par_farms_select_1_sum = 
    SWITCH(
        SELECTEDVALUE(Par_farms_select_1[Parameter1 Fields]),
        "'C_Farms'[number total]", SUM('C_Farms'[number total]),
        "'C_Farms'[area total]", SUM('C_Farms'[area total]),
        "'C_Farms'[number owned]", SUM('C_Farms'[number owned]),
        "'C_Farms'[area owned]",SUM('C_Farms'[area owned]),
        "'C_Farms'[number leased]", SUM('C_Farms'[number leased]),
        "'C_Farms'[area leased]", SUM('C_Farms'[area leased]),
        "'C_Farms'[number mixed/others]", SUM('C_Farms'[number mixed/others]),
        "'C_Farms'[area mixed/others]", SUM('C_Farms'[area mixed/others]))

This, though, seems clunky--I'll need to do it many times over for this project. Is there a more efficient way to do this, or is this it?

Appreciate your help,

Anton

[ant_lah]

I see--thanks for the explanation and the pointer!

[Ritaf1983 · Accepted solution]

Hi @ant_lah 

The issue you're facing is that field parameters are not actual columns but references to columns. They act as dynamic links rather than holding values themselves.

Because of this, they are great for filtering or displaying data as a dimension in visuals, but they cannot be used directly inside aggregation functions like SUM().

Instead of using a field parameter to reference a column for aggregation, create separate measures for each calculation and use a parameter to switch between the measures instead of switching between columns.

For example, instead of SUM(Par_farms_select_1[Par_farms_select_1]), which doesn't work because Par_farms_select_1 is a reference, you should define separate measures like this:

Total_Number = SUM('C_Farms'[number total]),

Total_Area = SUM('C_Farms'[area total]),

Total_Owned = SUM('C_Farms'[number owned]).

Then, create a new field parameter that switches between these measures, just as you would with columns. Instead of selecting columns when defining the parameter,  drag the measures into the parameter setup

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.