Forum Discussion

dinoscool3's avatar
dinoscool3
Helper II
4 years ago
Solved

Conditional Column based on other table

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:

EmailWebinar
[email protected]How to tie shoes
[email protected]How to tie shoes
[email protected]How to tie ties
[email protected]How to tie ties

 

Viewers:

EmailWatch startWatch endWebinar
[email protected]11:1211:15How to tie shoes
[email protected]11:1011:20How to tie shoes
[email protected]11:2011:30How to tie shoes
[email protected]12:1012:30how to tie ties

 

What I want in the registration table:

EmailWebinarWatched
[email protected]How to tie shoesYes
[email protected]How to tie shoesYes
[email protected]How to tie tiesYes
[email protected]How to tie tiesNo

 

 

 

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?

 

Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    4 years ago

    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" )

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    dinoscool3 ,

    New column Registration =

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

    return

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

    • dinoscool3's avatar
      dinoscool3
      Helper II
      4 years ago

      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.

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    4 years ago

    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" )