topic Re: Validating Columns Against Other Column in Dataset in DAX Commands and Tips

Validating Columns Against Other Column in Dataset

gsk96 — Fri, 11 Oct 2024 01:38:52 GMT

Validating Pizza IDs Against Pizza Names in Dataset

Hello everyone,

I’m working with a dataset where I need to ensure that the Pizza ID corresponds correctly to the Pizza Name. Specifically, I have multiple entries for the same pizza type with different IDs.

For example, the Pizza IDs for "The Barbecue Chicken Pizza" are:

bbq_ckn_s
bbq_ckn_m
bbq_ckn_l

I want to check whether these IDs are correctly associated with the Pizza Name "The Barbecue Chicken Pizza."

Could anyone suggest an efficient way to validate this within my dataset?

Here's the file, Pizza sales

Re: Validating Columns Against Other Column in Dataset

powerbiexpert22 — Fri, 11 Oct 2024 02:09:44 GMT

Hi gsk96 ,

you can create calculated column in power bi similar to shown below and filter your results to "True". please see below file for your reference

https://drive.google.com/file/d/1wpnoW7LMQdKMsqe9fvpU7mTbyaNNHSEP/view?usp=drive_link

pizzacompar =

SWITCH(

TRUE(),

LEFT(pizza_sales[pizza_name_id], 7) = "bbq_ckn" && pizza_sales[pizza_name]= "The Barbecue Chicken Pizza", "True",

LEFT(pizza_sales[pizza_name_id], 7) = "ital_supr" && pizza_sales[pizza_name] = "The Italian Supreme Pizza", "True",

"False"

)

Re: Validating Columns Against Other Column in Dataset

gsk96 — Sat, 12 Oct 2024 00:41:18 GMT

Hi powerbiexpert22 It is a tedious job as there are 32 pizza names and 96 pizza ids

Re: Validating Columns Against Other Column in Dataset

DataNinja777 — Sat, 12 Oct 2024 07:30:52 GMT

Hi gsk96 ,

Does your company's ERP system have a product data table (dimension table) that is normalized, so the pizza size is not included with each product entry? Or is the pizza size part of the product code? Since your pizza sales table is a fact table, you only need the Pizza ID in it. I recommend taking the pizza names from the product table, using the Pizza ID as a lookup key.

Best regards,

Re: Validating Columns Against Other Column in Dataset

gsk96 — Fri, 18 Oct 2024 10:24:30 GMT

Hi powerbiexpert22 and DataNinja777 I have sucessfully solved it and it is working right.

Here's how I did it

ComparisonResult =

IF(

LEFT([pizza_name_id], 3) = MID([pizza_name], 5, 3)

|| (SEARCH("bbq", [pizza_name_id], 1, 0) > 0 && SEARCH("barbecue", [pizza_name], 1, 0) > 0),

TRUE,

FALSE

)