GroupBy column based on Dropdown value

[alex93jansen]

I have the field Dropdown7, with three values matching column names in my table:

Table({ Value: "activityName"}, { Value: "activityName2"}, { Value: "activityTask"})

and the field PieChart2:

AddColumns(
    GroupBy(
        Filter(
            '[dbo].[tmtrak_dev_records]',
            submittedBy = userEmail,
            activityDate >= DatePicker1.SelectedDate,
            activityDate <= DatePicker2.SelectedDate
        ),
        "activityName",
        "GroupData"
    ),
    "totalActivityHours",
    Sum(
        GroupData,
        activityHours
    )
)

I would like for PieChart2 to GroupBy the selected column value of Dropdown7 (e.g. if "activityName2" is selected, GroupBy "activityName2"). However, setting the GroupBy formula as follow is throwing an error:

    GroupBy(
        Filter(
            '[dbo].[tmtrak_dev_records]',
            submittedBy = userEmail,
            activityDate >= DatePicker1.SelectedDate,
            activityDate <= DatePicker2.SelectedDate
        ),
        Dropdown7.SelectedText.Value, //Dropdown7.Selected.Value
        "GroupData"
    )

How can I achieve this?

[RusselThomas]

Hi @alex93jansen ,

The formula expects a column object at that point in the formula - The dropdown value is not a column object, it's a an array value - likely a text string (or a record field, depending on how you've defined your dropdown items) - so you'll have to manually test for each dropdown value to determine your groupby column.  You can use If() or Switch(), here's a generic layout example using Switch() and assuming your dropdown value is a text  value;

    GroupBy(
        Filter(
            '[dbo].[tmtrak_dev_records]',
            submittedBy = userEmail,
            activityDate >= DatePicker1.SelectedDate,
            activityDate <= DatePicker2.SelectedDate
        ),
        Switch(Dropdown7.SelectedText.Value, //Dropdown7.Selected.Value
          "dropdownvalue1", column1,
          "dropdownvalue2", column2,
          "dropdownvalue3", column3),
        "GroupData"
    )

Hope this helps,

Kind regards,

RT

[alex93jansen]

So after renaming my Dropdown7 values, I now have the following formula:

AddColumns(
    GroupBy(
        Filter(
            '[dbo].[tmtrak_dev_records]',
            submittedBy = userEmail,
            activityDate >= DatePicker1.SelectedDate,
            activityDate <= DatePicker2.SelectedDate
        ), 
        Switch(
            Dropdown7.SelectedText.Value, 
            "Activity", 
            "activityName",
            "Sub-Activity",
            "activityName2", 
            "Task", 
            "activityTask"
        ), 
        "GroupData"
    ),
    "totalActivityHours",
    Sum(
        GroupData,
        activityHours
    )
)

which throws the error:

"Expected literal text. We expect literal text at this point in the formula. The function 'GroupBy' has some invalid arguments"

[RusselThomas · Accepted solution]

Hi @alex93jansen ,

My bad - I should have tested this, but forgot that column literals can't be passed through functions that don't support them.  The output in the Switch is by default Text, it doesn't carry through the expected output of the wrapping function.

I'll spend some time on this later today to see what we can see, but my stated solution above won't work as is, sorry!

The workaround would be to wrap the entire groupby in the switch (i.e. move the switch to the top level and repeat the entire expression for each case) - something like as follows;

Switch(Dropdown7.SelectedText.Value, //Dropdown7.Selected.Value
          "dropdownvalue1", GroupBy(
                            Filter(
                            '[dbo].[tmtrak_dev_records]',
                            submittedBy = userEmail,
                            activityDate >= DatePicker1.SelectedDate,
                            activityDate <= DatePicker2.SelectedDate
                            ),column1, "GroupData"),
          "dropdownvalue2", GroupBy(
                            Filter(
                            '[dbo].[tmtrak_dev_records]',
                            submittedBy = userEmail,
                            activityDate >= DatePicker1.SelectedDate,
                            activityDate <= DatePicker2.SelectedDate
                            ),column2, "GroupData"),
          "dropdownvalue3", GroupBy(
                            Filter(
                            '[dbo].[tmtrak_dev_records]',
                            submittedBy = userEmail,
                            activityDate >= DatePicker1.SelectedDate,
                            activityDate <= DatePicker2.SelectedDate
                            ),column3, "GroupData")
)

I haven't tested this either, will check just now, but should work.

Kind regards,

RT