cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
alex93jansen
Helper II
Helper II

GroupBy column based on Dropdown value

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
RusselThomas
Microsoft
Microsoft

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

View solution in original post

3 REPLIES 3
RusselThomas
Microsoft
Microsoft

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
Helper II
Helper II

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
Microsoft
Microsoft

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

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (3,547)