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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
dinoscool3
Helper II
Helper II

Check if value in column A is in column B then check if Column C is in column D

I have two tables, one of registrations, one of watch data. I want to add a column to the registration that shows if they watched, however, the watch data table shows any time they watched, the data is not distinct, and I can't create a relationship between the two tables.

 

I'm not sure where I found this, but a month ago I came up with this

Watched = IF(registrations'[email] in VALUE('watch_events'[email]),IF('registrations'[webinar_id] in VALUE('watch_events'[webinar_id]),1,0),0)

 

but it seems to overcount. Everything I find to try to fix this only talks about comparing one column with another, and not bringing up the third and fourth.

 

How can I create a watch column in the registration table that checks if they watched a particular webinar?

2 REPLIES 2
v-henryk-mstf
Community Support
Community Support

Hi @dinoscool3 ,

 

To create a watch column in the registration table that checks if they watched a particular webinar, you can use the COUNTIFS function in Excel. This function allows you to count the number of times that a combination of values occurs across multiple columns in your data.

Here's an example of how you can use COUNTIFS to create the watch column:

1 First, create a new column in the registration table called watch.

2 In the first cell of the watch column, enter the following formula:

COUNTIFS(watch_events[email], registrations[email], watch_events[webinar_id], registrations[webinar_id])

This formula will count the number of times that the email and webinar_id combination from the current row of the registration table occurs in the watch_events table.

3 Copy the formula down to the other cells in the watch column to calculate the watch status for each registration.

4 Finally, you can use an IF formula to compare the value in the watch column to 1, and return Yes if the value is 1 and No if the value is 0. For example, in the first cell of the watch column, you can use the following formula:

result=IF(watch=1,"Yes","No")

I hope this helps! Let me know if you have any other questions.


If the problem is still not resolved, please provide detailed error information and test data. Looking forward to your reply.


Best Regards,
Henry


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

But I'm trying to do this in Power BI, not excel?

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

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