Reply
avatar user
Anonymous
Not applicable
Partially syndicated - Outbound

Compare one to many column values between two tables

Hello,

 

I have a Students table that lists each UserID value once.

I want to know in a new column in the Students table if the UserID field has any matching values in the Infractions table.

It can either be the UserID value or a 1 for a match or a 0 for a non-match.

In the Infractions table, there can be many rows or no rows at all that match the UserID field from the Student table.

 

PBIX file with two tables mentioned above

Click the download icon to save the file 

download.jpg

 

Excel example of the result I am looking for.

vlookup.jpg

1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

Syndicated - Outbound

Thank you for posting the data.

If you want to create a calculated column in the Students table, the existing relationship makes things reasonably straightforward.

 

NewCol = CALCULATE(COUNT(Infractions[Infraction]))

 
That will give you the count for each student. If you want a 1 or 0 you just edit the formula using an IF statement.

Let me know if that helps.

View solution in original post

2 REPLIES 2
avatar user
Anonymous
Not applicable

Syndicated - Outbound

@HotChilli That worked perfectly! Thank you very much for your time and expertise. I like that it gave me a count of the infractions. Take care!

HotChilli
Super User
Super User

Syndicated - Outbound

Thank you for posting the data.

If you want to create a calculated column in the Students table, the existing relationship makes things reasonably straightforward.

 

NewCol = CALCULATE(COUNT(Infractions[Infraction]))

 
That will give you the count for each student. If you want a 1 or 0 you just edit the formula using an IF statement.

Let me know if that helps.

avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)