Measure to check if multiple values exist in another table

[tina_belcher123]

Hey Everyone! I've been stuck on this one for longer than i'd like to admit. I have 2 data sets that I would like to compare multiple values against. Basically, I want Power BI to check if each email in one table exists in another "control table". If yes, move on to the other check, if not then make the bar yellow. I've managed to get to this point: 

BarColour = IF(CONTAINS('Control Table', 'Control Table'[Business Email], SELECTEDVALUE('Data Input Table'[Participantemail])), IF([TotalParticipantsPercentage] >= [DeptGoal], "Green", "Red"), "Yellow")

I realize through testing that the "SELECTEDVALUE" is likely checking for only one value instead of the multiple value check I want it to perform. Is there a way I can get this to check each row of data against each row of data in the other table? Thanks in advance 🙂

[TheoC · Accepted solution]

Hi @tina_belcher123 

Hopefully this meets your needs:

BarColour = 

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

VAR _2 = IF ( NOT ( ISBLANK ( _3 ) ) , [_TotalParticipantPercentage], BLANK () )

RETURN

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

Let me know if it needs adjustment.

Thanks heaps,

Theo

[tina_belcher123]

Thank you @TheoC, this is genius! Headache gone *phew!

The only thing I changed was I swapped the 'Control Table' and 'Data Input Table' where mentioned and it worked perfectly! I assume this is based on what table you want the "true values" to be and which table holds the "potential error"? If you have the time I would love more detail into how you built out the code. Any insight is helpful. Thanks again!

[TheoC]

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 

[tina_belcher123]

Thank you @TheoC, this breaks it down perfectly! I really appreciate your help with this and walking me through each component. 🙂

[parry2k]

@tina_belcher123 It will be easier if you share pbix file using one drive/google drive with the expected output. Remove any sensitive information before sharing.