Group Data shown in Gallery based on related table field

[Poweruser1101]

Hi All,

I have an embedded canvas app on my Model Driven Form.I have subgrid on my Form in dataverse and the subgrid table has a lookup of another table and I want to display data of the subgrid in a gallery control and apply grouping to it.

As shown in the image, 'Risk Grade Assessment' is the main table on which I have a subgrid of 'Risk Grade Assessment Question' table.'Risk Grade Assessment Question' has a look up of 'Risk Grade Question' and 'Risk Grade Question' table has a lookup of 'Category'.

I want to group my gallery based on Category and want to count the questions against it but since the category is not available in 'Risk Grade Assessment Question' table I am unable to do grouping

Data to be displayed in the gallery

Category	Count of Questions
Asset Characteristics	3
Market Condition	1

I tried creating a collection like the below and I want to add the Category Column in the collection so that I can use it for grouping in the gallery

ClearCollect(RGAssessmentQuestions, ShowColumns(Filter('Risk Grade Assessment Questions', ThisRecord.'Risk Grade Assessment'.'CS Risk Grade Assessment'=[@ModelDrivenFormIntegration].Item.'CS Risk Grade Assessment'),"ice_RiskGradeQuestion","ice_question","ice_response"));

Also tried without collection directly setting the items property of the gallery as follows but not sure how to get Category here and also apply grouping -

Filter('Risk Grade Assessment Questions', ThisRecord.'Risk Grade Assessment'.'CS Risk Grade Assessment'=[@ModelDrivenFormIntegration].Item.'CS Risk Grade Assessment')

And i can display category using the below formula in the label in the gallery

LookUp('Risk Grade Questions',ThisRecord.'Risk Grade Question'=GUID(ThisItem.ice_RiskGradeQuestion.'Risk Grade Question')).Category.Category

Kindly suggest.