Quality check from Form and Excel file

romovaro Use DAX (Data Analysis Expressions) to create a custom column that checks if the Capacity Bucket from the form matches any of the buckets in the Excel file.

DAX
BucketMatch =
VAR FormBucket = 'FormData'[Bucket]
VAR CountryCode = 'FormData'[Country code]
VAR Bucket1 = LOOKUPVALUE('ExcelData'[Bucket1], 'ExcelData'[Country code], CountryCode)
VAR Bucket2 = LOOKUPVALUE('ExcelData'[Bucket2], 'ExcelData'[Country code], CountryCode)
VAR Bucket3 = LOOKUPVALUE('ExcelData'[Bucket3], 'ExcelData'[Country code], CountryCode)
RETURN
IF(
FormBucket = Bucket1 || FormBucket = Bucket2 || FormBucket = Bucket3,
"Match",
"No Match"
)

You can now create a visual in Power BI to display the results of the BucketMatch column, showing which entries match and which do not.

Hi  romovaro

I replicated your scenario in Power BI Desktop.    

 
NOTE: In the below example, I am assuming you want to compare values in ISLOT Extract table from Bucket table.  

 
Below is the steps and recommendation which may help here: 
 

Step –1: Right click on ISLOT table and click on New column. 
 

Step –2: Paste the below code. 
 

Bucket Comparison =  

IF( 

    'ISLOT Extract'[Capacity Bucket] IN VALUES('Bucket Table'[Bucket1]) || 

    'ISLOT Extract'[Capacity Bucket] IN VALUES('Bucket Table'[Bucket2]) || 

    'ISLOT Extract'[Capacity Bucket] IN VALUES('Bucket Table'[Bucket3]), 

    "Correct", 

    "Mismatch" 

) 
 

After pasting the above code, it will look like below: 
 

This formula checks if the Capacity Bucket from 'ISLOT Extract' is present in any of the Bucket1, Bucket2, or Bucket3 columns in the 'Bucket Table' for the corresponding Country Code. If a match is found, it returns "Correct". Otherwise, it returns "Mismatch". 

Below is what it will look like in the table view: 
 
NOTE: I have purposely not loaded data for PM AMER and PMC AMER in Bucket table only so that, I can show some mismatch values as well working as per the code. 

If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.