Answered

Grouping by and counting values in Lookup Columns

(1) Share

Report

Posted on by ben_E_556

Hi,

I have a table 'Application Reviews' with columns 'Application ID' and 'Reviewer'. 'Application ID' is a lookup column to table 'Applications', and 'Reviewer' is a lookup column to table 'Reviewers'. I'm trying to create a text box in my canvas app that displays the number of applications that have been reviewed by more than 1 reviewer. I've tried the following below, but I get an error that 'Application ID' "expects a text, numeric, boolean, or date/time value".

Really appreciate any help!

CountIf(
AddColumns(
GroupBy('Application Reviews', 'Application ID', GroupedAppIDs), 
DistinctReviewers, 
CountRows(
Distinct(GroupedAppIDs, Reviewer)
)
), 
DistinctReviewers > 1
)

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (1)

Verified answer

WarrenBelz 153,075 Most Valuable Professional on at

Like (1)

Report
Hi @ben_E_556,

You need to add a column for the lookup fields as below (I assume you are using SharePoint here)
CountIf( AddColumns( GroupBy( AddColumns( 'Application Reviews', AppID, 'Application ID'.Value, Review, Reviewer.Value ), AppID, GroupedAppIDs ), DistinctReviewers, CountRows( Distinct( GroupedAppIDs, Review ) ) ), DistinctReviewers > 1 )

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
MVP (Business Applications) Visit my blog Practical Power Apps LinkedIn Buy me a coffee

Was this reply helpful? Yes No
ben_E_556 40 on at

Like (0)

Report
Got it to work using Warren's reply and changing the .Value to .application_id and .reviewer. Thanks!

CountIf( AddColumns( GroupBy( AddColumns( 'Application Reviews', AppID, 'Application ID'.application_id, Review, Reviewer.reviewer ), AppID, GroupedAppIDs ), DistinctReviewers, CountRows( Distinct( GroupedAppIDs, Review ) ) ), DistinctReviewers > 1 )

Was this reply helpful? Yes No