Quality check from Form and Excel file

[romovaro]

Hi

I am trying to biuld a formula to check if the Capacity Bucket is showing the correct information from a Microsfot Form

I have an Excel file with countries and bucket Options. SOme countries have 2 Buckets and other 3 buckets.

Using the country Code as link, I need to compare what the form says vs the options from the excel and detect if different.

Region	Contry	Country code	Bucket1	Bucket2	Bucket3
AMER	Antigua & Barbuda	AG	PMC AMER	PM AMER
AMER	Argentina	AR	PMC AMER	PM AMER
AMER	Aruba	AW	PMC AMER	PM AMER
AMER	Bahamas	BS	PMC AMER	PM AMER
AMER	Uruguay	UY	PMC AMER	PM AMER
AMER	Venezuela	VE	PMC AMER	PM AMER
APAC	Australia	AU	PMC APAC	PM APAC
APAC	Bangladesh	BD	PMC APAC	PM APAC
APAC	Cambodia	KH	PMC APAC	PM APAC
APAC	Brunei	BN	PMC APAC	PM APAC
APAC	China	CN	PMC APAC	PM APAC
APAC	Fiji	FJ	PMC APAC	PM APAC
APAC	Guam	GU	PMC APAC	PM APAC
APAC	Hong Kong	HK	PMC APAC	PM APAC
APAC	India	IN	PMC APAC	PM APAC
APAC	Vietnam	VN	PMC APAC	PM APAC
EMEA	Albania	AL	PMC EMEA	PM EMEA	SBS EMEA
EMEA	Algeria	DZ	PMC EMEA	PM EMEA	SBS EMEA
EMEA	Angola	AO	PMC EMEA	PM EMEA	SBS EMEA
EMEA	Armenia	AM	PMC EMEA	PM EMEA	SBS EMEA
EMEA	Austria	AT	PMC EMEA	PM EMEA	SBS EMEA
EMEA	Azerbaijan	AZ	PMC EMEA	PM EMEA	SBS EMEA

That's the relationship

Any help is welcome

Thanks,

[bhanu_gautam · Accepted solution]

@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.

[Ray_Minds · Accepted solution]

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.

[bhanu_gautam · Accepted solution]

@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.