Help with table filtering for Gallery Items, Dataverse table and A dropdown

[RandomDept]

Good morning, all.

I have been going around in circles trying to get the right syntax on this one.

I have a Dataverse Table (Monthly Quota Usage) with the following Fields:

Environment Name (Text)

Capacity Type (Text, "Log", "File" or "Database", note this is indeed Text and not choice.

Actual Usage (Decimal, this holds the usage for each Capacity type, ie the table has multiple rows for an environment, one row will have the 'Capacity Type' as Log, the next File etc, and the Actual Usage on that row corresponds)
Month Ending (Date only)

My dropdown is called Environment_DD and has the values of the Environment names.

I want to populate the gallery so that firstly looks at the Month Ending dates, subtracts 1 month and converts them to mmm yyyy then group them. Ie this filter should control how many rows we would see in the gallery. And they need to order from oldest to newest.

I create a collection with the OnChange Property of the Environment_DD using the below:

ClearCollect(

    colGroupedData,

    GroupBy(

        'Monthly Environment Quota Usages',

        'Environment Name',

        'Month Ending',

        'Capacity Type',

        'Actual Consumption'

    )

)

It looks like the Actual Consumption field in my collection isn't getting populated so there is clearly an error above. I guess I need to specify Log, File or Database type? Additionally, I should only collect data matching the Envrionment_DD.Selected.Value = Environment Name. (I had that in at some point, but as I mentioned, playing the circle game).

They gallery items property is:

SortByColumns(

    Distinct(

        Filter('Monthly Environment Quota Usages', 'Environment Name' = Environment_DD.Selected.Value),

        'Month Ending'

    ),

    "Value",

    SortOrder.Descending

)

I have added in more text fields in the gallery view as required so it shows as

Environment Name       Month Ending      Log File Usage        File Usage      Database Usage    Total Usage

If there are no more dates, that should be it.

I get the total usage by adding the textbox names of Log File Usage, File Usage and Database Usage together. (Which I could probably do before hand in the collection, but this at least seems to work).

I have my date in the gallery formatted with:

Text(DateAdd(First('Monthly Environment Quota Usages').'Month Ending', -1, TimeUnit.Months), "mmm yyyy")

I know this is grabbing it from the actual table and not collection, but I could not get the syntax to work with the collection.

Ie tried

Text(DateAdd(First('ThisItem.'Month Ending', -1, TimeUnit.Months), "mmm yyyy") etc.

Again I think this needs to be done in the initial collection / filter and I should be able to ThisItem.'Month Ending'?

Sorry, there's a slot there.

Greatly appreciate any assistance 🙂

Thank you

RD

[v-yueyun-msft · Accepted solution]

Hi , @RandomDept 

I test it in my side , this is my dataverse test table:

And this is the result in my Power Apps when i selected the "Mont Test":

Gallery Items:

 Distinct(  Filter('Monthly Environment Quota Usages', 'Environment Name' = Environment_DD.Selected.Value) , Text( DateAdd( 'Month Ending' ,-1,TimeUnit.Months) , "mmm yyyy") )

Label in Gallery:

ThisItem.Value

"Log Usgae: "&Coalesce(Sum( Filter('Monthly Environment Quota Usages', 'Environment Name' = Environment_DD.Selected.Value , 'Month Ending'>=DateAdd( DateValue(ThisItem.Value),1,TimeUnit.Months) ,'Month Ending' < DateAdd( DateValue(ThisItem.Value),2,TimeUnit.Months) ,'Capacity Type'="Log" ) , 'Actual Usage'),0)

"File Usgae: "&Coalesce(Sum( Filter('Monthly Environment Quota Usages', 'Environment Name' = Environment_DD.Selected.Value ,'Month Ending'>=DateAdd( DateValue(ThisItem.Value),1,TimeUnit.Months) ,'Month Ending' < DateAdd( DateValue(ThisItem.Value),2,TimeUnit.Months) ,'Capacity Type'="File" ) , 'Actual Usage'),0)

"Database Usgae: "&Coalesce( Sum( Filter('Monthly Environment Quota Usages', 'Environment Name' = Environment_DD.Selected.Value ,'Month Ending'>=DateAdd( DateValue(ThisItem.Value),1,TimeUnit.Months) ,'Month Ending' < DateAdd( DateValue(ThisItem.Value),2,TimeUnit.Months) ,'Capacity Type'="Database" ) , 'Actual Usage') ,0)

"Total Usgae: "&Coalesce(Sum( Filter('Monthly Environment Quota Usages', 'Environment Name' = Environment_DD.Selected.Value ,'Month Ending'>=DateAdd( DateValue(ThisItem.Value),1,TimeUnit.Months) ,'Month Ending' < DateAdd( DateValue(ThisItem.Value),2,TimeUnit.Months) ) , 'Actual Usage'),0)

If this reply can help you , you can click mark this reply as solution (Accept solution) which can help more people, thanks in advance! 

Best Regards,

Yueyun Zhang

[RandomDept]

Thank you so much,

All working, you are a legend!