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

Lookups in GroupBy

Hi all. I'm having issues using data typed as Lookup in a GroupBy for aggregation. I've checked the other related posts on the board, but they seem to be pretty specific to their scenario, and I'm not good enough yet to get them to apply to my case. Datasource is Dataverse.

 

The GroupBy formula I have, which will be used as the Items property in a gallery, is as follows:

AddColumns(
    GroupBy(
        Filter('Time Entries','Time Entries (Views)'.'Time Entries for Approval',Date > VarSelectedStartDate && Date < VarSelectedEndDate),
        "'Bookable Resource'.Name","Project.'Project Name'", "Date", "Prj Data"),"Hours",Sum('Prj Data',Duration))

 However, 'Bookable Resource'.Name and Project.'Project Name' are data type Lookup. I'd imagine the solution would involve nested AddColumns to create a "new" value for Resource Name and Project Name that can be referenced in the GroupBy, but that's beyond my current skill level. Can anyone assist?

A side question: can I encode 'Time Entries','Time Entries (Views)'.'Time Entries for Approval' into a variable for easier and more efficient use in formulas? Something similar to using VAR in Power BI measures.

 

I've had a lot of questions on here lately, and I anticipate having more, but people have been incredibly helpful, so thanks to everyone. 

1 ACCEPTED SOLUTION

Accepted Solutions

I was able to get it working with the following formula:

 

SortByColumns(
    AddColumns(
        GroupBy(
            AddColumns(
                Filter(
                    'Time Entries',
                    'Time Entries (Views)'.'Time Entries for Approval',
                    Date > VarSelectedStartDate && Date < VarSelectedEndDate
                ),
                "DateString",
                Text(Date, ShortDate),
                "ResourceName",
                'Bookable Resource'.Name,
                "ProjectName",
                Project.'Project Name'
            ),
            "DateString",
            "ResourceName",
            "ProjectName",
            "Prj Data"
        ),
        "Minutes",
        Sum(
            'Prj Data',
            Duration
        )
    ),
    "DateString",
    Ascending
)

View solution in original post

3 REPLIES 3
KroonOfficeSol
Resident Rockstar
Resident Rockstar

You could try this:

With(
    {
        table: 
            Filter('Time Entries','Time Entries (Views)'.'Time Entries for Approval'
                ,Or(IsBlank(VarSelectedStartDate), Date > VarSelectedStartDate) 
                ,Or(IsBlank(VarSelectedStartDate), Date < VarSelectedEndDate)
            )
    }
    ,AddColumns(
        GroupBy(
            AddColumns(table, "BookableResourceName", "'Bookable Resource'.Name","ProjectName", "Project.'Project Name'")
            ,"BookableResourceName","ProjectName", "Date", "Prj Data"
        ),"Hours",Sum('Prj Data',Duration)
    )
)

Know that you lose pagination and will load in all matching records.

For the second question on connection name the answer is you can't. But what you can do is take away the second and third part of and add the conditions which you apply in the dynamics view manually in your Filter formula.

Hi @KroonOfficeSol,

 

In your formula, when I'm creating the columns BookableResourceName and ProjectName, putting double quotes around 'Bookable Resource'.Name and Project.'Project Name' turns them into strings and makes every entry in the gallery that text:   

RMDNA_0-1617223371801.png

 

However, if I take the double quotes out, it just doesn't work: my only choices are all the non-lookup fields in that view. Even then, choosing any of those fields is what breaks the formula.

 

In any case, I tried both 'Bookable Resource'.Name, which is how I refer to it in my un-aggregated gallery (when using ThisItem), and also its backend name 'msdyn_bookableresource'. Am I misunderstanding something about my data?

 

RMDNA_1-1617223613554.png

 

On the side: this seems like an incredibly complicated solution to what I figured would be a simple problem. Are Lookup fields just bad practice? Is aggregation via GroupBy a really bad approach?

I was able to get it working with the following formula:

 

SortByColumns(
    AddColumns(
        GroupBy(
            AddColumns(
                Filter(
                    'Time Entries',
                    'Time Entries (Views)'.'Time Entries for Approval',
                    Date > VarSelectedStartDate && Date < VarSelectedEndDate
                ),
                "DateString",
                Text(Date, ShortDate),
                "ResourceName",
                'Bookable Resource'.Name,
                "ProjectName",
                Project.'Project Name'
            ),
            "DateString",
            "ResourceName",
            "ProjectName",
            "Prj Data"
        ),
        "Minutes",
        Sum(
            'Prj Data',
            Duration
        )
    ),
    "DateString",
    Ascending
)

View solution in original post

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (63,538)