cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mrQ
Helper V
Helper V

filter some items from data source

Dear community

 

I have a dataverse for teams table called Registered Users. It has a column called Azure AD Object ID

Based on another table called Not Valid, I created a collection colNotValid. It has a lookup to the Registered Users table.

 

In my gallery, I would like to show all items in Registered Users WITHOUT the ones that are in the colNotValid collection (column 'cr466_User2').

 

 

Filter(
    'Registered Users', 
    Not(
        'Azure AD Object ID' in colNotValid.cr466_User2.'Azure AD Object ID'
    )
)

// tried the following alternative, but didn't work either (Registered Users is the unique identifier of 'Registered Users'

Filter(
    'Registered Users', 
    Not(
        'Registered Users' in colNotValid.cr466_User2.'Registered Users'
    )
)

 

 

 

Above code doesn't do that and I don't know why. It simply shows ALL users from Registered Users. If I remove the Not() it won't show any users. What am I doing wrong?

 

Thanks in advance for your help.

 

**edit**

This feature is activated but the behavior is the same with collections directly.

feature-active.png

1 ACCEPTED SOLUTION

Accepted Solutions
v-albai-msft
Microsoft
Microsoft

Hi @mrQ ,

How do you create the collection colNotValid?  Using formula like below(may should be “Not Valids”)?

Collect(colNotValid, 'Not Valid')

If yes, try this to create this collection(save the lookup column into a new column called "test2"):

Collect(colNotValid, AddColumns('Not Valid',"test2",cr466_User2.'Azure AD Object ID'))

Then in your gallery, try this:

Filter(
    'Registered Users',
    Not(
       'Azure AD Object ID' in colNotValid.test2
    )
)

Best regards,

Allen

View solution in original post

5 REPLIES 5
dpoggemann
Solution Sage
Solution Sage

Hi @mrQ,

 

Can you try the following for the filter:

Filter( 'Registered Users', !('Azure AD Object ID' in 'Not Valid'.cr466_User2))

 

I am doing this from memory so hopefully I got it right 😀

 

Hope this helps.

 

Thanks,


Drew

v-albai-msft
Microsoft
Microsoft

Hi @mrQ ,

How do you create the collection colNotValid?  Using formula like below(may should be “Not Valids”)?

Collect(colNotValid, 'Not Valid')

If yes, try this to create this collection(save the lookup column into a new column called "test2"):

Collect(colNotValid, AddColumns('Not Valid',"test2",cr466_User2.'Azure AD Object ID'))

Then in your gallery, try this:

Filter(
    'Registered Users',
    Not(
       'Azure AD Object ID' in colNotValid.test2
    )
)

Best regards,

Allen

View solution in original post

Thanks! That worked!

I just don't get why I need to save it to a new column when this information is already there in a relational database...?

Hi @mrQ ,

Glad you have solved your issue.

Actually, I cannot find an official document mentioned that why we cannot directly use formula like "Table2.lookup.Name". So each time I have your issue, I will keep the lookup result into a new column. And this did work😁

Best regards,

Allen

Sounds to me like a bug... 😕

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.

Top Solution Authors
Users online (55,764)