Measure to check if multiple values exist in another table

Hi tina_belcher123 

It's a pleasure and glad the headache is gone, lol!

Thank you for making that amendment and apologies for the that oversight on my part.

In terms of the measure I put forward, it may seem like a bit is happening but hopefully the breakdown below can make sense of it all.

VAR 1

CALCULATE ( 
    FIRSTNONBLANK ( 'Control Table'[Business Email] , 1 ) , 
    FILTER ( ALL ( 'Control Table' ) , 'Control Table'[Business Email] IN VALUES ( 'Data Input Table'[Participantemail] ) ) 
)

Breaking it down into further steps:

FIRSTNONBLANK

FIRSTNONBLANK ( 'Control Table'[Business Email], 1 )

This function retrieves the first non-blank value in a column. In this case, it’s looking for the first email in the Control Table that matches the participant's email from the Data Input Table.  We use FIRSTNONBLANK because we only care about a single occurrence of the email being present in both tables. So, even if there are multiple matches, one the criteria of a single match is identified, we are telling Power BI, "Nice work, buddy! Now, let's move on to the next step!"

The next part of the measure is:

FILTER ( ALL ( 'Control Table' ) , 'Control Table'[Business Email] IN VALUES ( 'Data Input Table'[Participantemail] ) )

FILTER

FILTER applies row-level filtering to a table. By using it, we are literally telling Power BI to ignore everything else and to only return rows that meet the specified condition we have set.

ALL

ALL ( 'Control Table' )

We use ALL in this context to ensure the entire Control Table is evaluated without any prior filters that might be applied. Basically, it tells Power BI that we want it to consider EVERYTHING (ALL) in the Control Table which can be critical when filtering for matches across the entire table rather than a subset of the records within a table.

IN VALUES

'Control Table'[Business Email] IN VALUES ( 'Data Input Table'[Participantemail] )

The IN operator is essentially telling Power BI that we are working with a list of VALUES, and it checks whether each value in a specified column exists within that list. It's a way checking if a value from one column is present in a set of values from another column (list).  So basically, when combining them together, we have IN that checks if the Business Email is found within the Participantemail values, and then VALUES ensures we are only comparing the distinct participant emails.

VAR 2

IF ( NOT ( ISBLANK ( _3 ) ) , [_TotalParticipantPercentage], BLANK () )

Breaking it down into further steps:

IF

IF is just a standard conditional function, establishing the context of if something is TRUE then do this otherwise if it is FALSE then do that.  In our measure, we are saying if the condition is met, return [_TotalParticipantPercentage].  If the condition is not met, then return BLANK().

NOT ( ISBLANK ( _3 ) )

The NOT function in this context reverses the preceeding boolean value. In this case, we are saying, "If the output of the first variable is NOT BLANK then return the [_TotalParticipantPercentage]".

RETURN

RETURN

SWITCH ( 
    TRUE () , 
    ISBLANK ( _3 ) , "Yellow" , 
    _2 >= [DeptGoal] , "Green" , 
    _2 < [DeptGoal] && NOT ( ISBLANK ( _2 ) ) , "Red" , 
    "Yellow" 
)

SWTICH ( TRUE () 

SWITCH( TRUE() ) is something I use all the time when working with conditionals.  It pretty much allows you to evaluate as many conditions as you want with clarity and simplicity.  Imagine an IF statement that only considered whether a condition was TRUE and, it is wasn't, it would just go to the next condition to consider whether it was TRUE.... and then again, rinse and repeat, until you have no further conditions you want tested, at which point you can close it off with the FALSE output. 

In our measure, we are saying:

1. IF VAR _3 is BLANK then "Yellow" 
2. IF VAR _2 is GREATER THAN OR EQUAL TO the [DeptGoal] then "Green"
3. IF VAR _2 is LESS THAN the [DeptGoal] AND VAR _2 is NOT BLANK then return "Red"
4. Otherwise, if none of the above are TRUE then return "Yellow".

I hope this helps and provides additional context to the measure!  And please reach out with any additional questions!

All the best and speak soon! 🙂

Theo