Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
jrmaier
Helper IV
Helper IV

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

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
jrmaier
Helper IV
Helper IV

@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

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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