Conditional Column based on other table

[dinoscool3]

I have a table of registrations and a table of viewers. I would try to add them as a relationship but because of how the data is stored and the number of webinars, registrations, and viewers, I can't get it to work. So I'm wondering if I can find a way this way. (Tables simplified)

Registration:

Email	Webinar
georgeburns@gb.com	How to tie shoes
bobhope@bh.com	How to tie shoes
jackbenny@jb.com	How to tie ties
donrickles@dr.com	How to tie ties

Viewers:

Email	Watch start	Watch end	Webinar
georgeburns@gb.com	11:12	11:15	How to tie shoes
bobhope@bh.com	11:10	11:20	How to tie shoes
Georgeburns@gb.com	11:20	11:30	How to tie shoes
jackbenny@jb.com	12:10	12:30	how to tie ties

What I want in the registration table:

Email	Webinar	Watched
georgeburns@gb.com	How to tie shoes	Yes
bobhope@bh.com	How to tie shoes	Yes
jackbenny@jb.com	How to tie ties	Yes
donrickles@dr.com	How to tie ties	No

I think the idea would be:

For each row in the Registartions table:

1. If a match exists between Registrations[email] and Viewers[email]
   1. If registrations[webinar_id] equals viewers[webinar_id]
      
      1. Set registrations[watched] to "Yes"
      2. Else set Set registrations[watched] to "No"

Unless I'm missing something here?

[jdbuchanan71 · Accepted solution]

@dinoscool3 

Try adding this as a calculated column in your Registrations table.

Watched = 
VAR _ViewCount = 
CALCULATE (
    COUNTROWS ( Viewers ),
    TREATAS (
        CALCULATETABLE (
            SUMMARIZE ( Registrations, Registrations[Email], Registrations[Webinar] )
        ),
        Viewers[Email],
        Viewers[Webinar]
    )
)
RETURN IF ( _ViewCount > 0, "Yes", "No" )

[amitchandak]

@dinoscool3 ,

New column Registration =

var _1 = Maxx(filter(Viewers, Viewers[Email] =Registration[email]), Viewers[Email])

return

if(isblank(_1), "No", "Yes")

[dinoscool3]

When I try to do this, I can't add in the registration email in the filter, it only lets me add in values from the viewers table.