Return records for slice with related table with m...

Michel_LeBlond · 12-07-2020 06:17 PM

I want a slice for a calendar view but have been having trouble returning records when including a related table with multiple related records.

The goals table has an allies sub table that can have many related records .
This expression only returns a single record.
[ID] = ANY(SELECT(Allies[GoalID], [EmailAddress] = USEREMAIL()))
I have tried many other ways with lookups and always hit the same thing.

This similar expression is working for a slice for the same goals table against the client table that is a parent of the goals table.
[ClientID] = ANY(SELECT(Client[ID], [GmailAddress] = USEREMAIL()))

I suspect that there is a super simple way to do what I need but have not found it. Please help with the answer if you know it. Feel free to ask clarifying questions.

Suvrutt_Gurjar

Hi @Michel_LeBlond,

Applying ANY() converts the list created through SELECT() into a single element list. As a result, the expression returns a single record.

You will need to have a multi-element list . such as with only SELECT() to return multiple records in comparison.

Michel_LeBlond

Thanks Suvrutt,
Yes I was aware that any grabs just one but there is a challenge.
Removing any so the expression looks like this [ID] = SELECT(Allies[GoalID], EmailAddress] = USEREMAIL())
Throws this error.
Table slice ‘AllyGoalsSlice’ uses an invalid filter condition ‘=[ID] = SELECT(Allies[GoalID], [EmailAddress] = USEREMAIL())’. Cannot compare Text with List in ([ID] = SELECT(Allies[GoalID],([EmailAddress] = USEREMAIL())))

How do I change the left side of the expression to be asking for a list of IDs

Suvrutt_Gurjar

Please try

IN([ID], SELECT(Allies[GoalID], [EmailAddress] = USEREMAIL()))

Michel_LeBlond

Perfect, I was trying permutations of that with = and looking at post Cannot compare List with Text in ([_ComputedKey]

Thanks for being the bringer of clarity !

Return records for slice with related table with multiple related records