True or False Conditional formatting inside the same tables and DAX

[Anonymous]

Hi,

How can I compare the multiple column inside the same tables via Power BI formula and conditional formating?

I have a one Table that contains text and numbers from column B:I. 

Column B:E name called Table A and column F:I called Table B.

Column J is my output "YES" or "NO".

IF matched columns from B:E into the columns F:I then return the output is "YES" in column J and If not matched any of these columns then return the output is "No" in column J.

Data:

TABLE A	TABLE B
FRUIT LIST	AREA CODE	LANGUAGE	SALES CODE	FRUIT LIST	AREA CODE	LANGUAGE	SALES CODE	STATUS
Avocado	3100	EN	EU01	Avocado	3100	EN	EU01	YES
Avocado	3100	EN	US	Avocado	3100	EN	US	YES
Avocado	3100	EN	UK	Avocado	3100	EN	UK	YES
Avocado	WEST	EN	UK	Avocado	3100	EN	UK	NO
Avocado	3100	EN	UK	Avocado	3100	EN	UK	NO
Avocado	3100	EN	UK	Avocado	3100	EN	UK	NO
Avocado	3100		UK	Avocado	3100	EN	UK	NO
Avocado	3100	EN	INDIA01	Avocado	3100	EN	UK	NO
Avocado	3100	EN		Avocado	3100	EN	UK	NO
Star Fruit	WEST	EN	INDIA01	Star Fruit	WEST	EN	INDIA01	YES
Star Fruit	NORTH	EN	INDIA01	Star Fruit	NORTH	EN	INDIA01	YES
Star Fruit	EAST	EN	INDIA01	Star Fruit	EAST	EN	INDIA01	YES
Star Fruit	SOUTH	EN	INDIA01	Star Fruit	SOUTH	EN	INDIA01	YES
Star Fruit	WEST	EN	INDIA02	Star Fruit	WEST	EN	INDIA02	YES
Star Fruit	NORTH	EN	INDIA02	Star Fruit	NORTH	EN	INDIA02	YES
Star Fruit	EAST	EN	INDIA02	Star Fruit	EAST	EN	INDIA02	YES
Star Fruit	SOUTH	EN	INDIA02	Star Fruit	SOUTH	EN	INDIA02	YES
Watermelon		EN	UK	Watermelon		EN	UK	YES

[BA_Pete]

Hi @Anonymous ,

When you say "Power BI formula", do you mean Power Query M code? If so, have you imported these tables into Power Query and what do they look like if you have?

Pete

[Anonymous]

thanks for your reply. Yes tge data are uploaded in power Bi. 

The power BI formula : I mean by creating measure.

what mean by Power query M code? Sorry I am new to power BI. Please explain. 

[Anonymous]

If possible to achieve my output through the conditional formatting (add column - - - conditional formatting)? Please advice. 

[BA_Pete]

@Anonymous 

Power Query M language is what you use to make transformations to your data, within Power Query in Power BI Desktop, before you send it to the data model.

When you say "Uploaded to Power BI" do you mean you have uploaded your excel file exactly as your screenshot shows as a dataset on the Power BI webpage, or you have imported the Excel file into Power BI Desktop?

And when you ask about conditional formatting, based on your required outcome, I believe you mean a conditional column. This can be done quite easily in a number of ways, but I need to understand whereabouts in Power BI your data is and what format it is in so that I can give you the right solution, hence all the questions. 

Pete

[Anonymous]

Thanks for reply and explain.

Here is the file for your reference...

https://www.dropbox.com/s/ljsjchxy4izhqkk/POWER%20BI%20IF%28PET%29.pbix?dl=0

Please advise multiple solutions if it possibile.

status is my output column. 

How I matached the output:

Fruit list=Fruit list

Area code=Area code

language=Languge

Sales code=Sales code4

 if all of them matched then I need result is "Yes" or "No"

[BA_Pete]

Hi @Anonymous ,

On the ribbon, go to Transform Data to open Power Query.

In Power Query, go to the Add Column tab then click Custom Column.

Enter this calculation:

if [FRUIT LIST] = [FRUIT LIST_1] and
[AREA CODE] = [AREA CODE_2] and
[LANGUAGE] = [LANGUAGE_3] and
[SALES CODE] = [SALES CODE_4]
then "YES" else "NO"

Pete

[Anonymous]

Hi,

Thanks for your reply. it's working but I have small issue when I have blanks columns. How can I add isblanks or ifblanks in this same calculations. 

Please advise.

[BA_Pete]

@Anonymous 

What output do you want if there's blanks?

Are there any columns that never have blanks?

Do you want different output depending on which column is blank?

Pete

[Anonymous]

thanks for replay back again. attatched snapshot for your reference.

https://www.dropbox.com/s/7x41x96pnq1eqqc/error1%20%28pet%29.png?dl=0

Example;

[FRUIT LIST] = [AREA CODE] = [LANGUAGE] = [SALES CODE_4] =are equal to "blanks" then return the output is "NA"

[Anonymous]

Hi,

Please ignore the previous reference I attached the wrong snapshot.could you please consider this one. Herewith atatched the file and snapshot for your reference.

https://www.dropbox.com/s/6tixr2l2oqepo8h/ERROR2.png?dl=0

https://www.dropbox.com/s/o74o4tl4fn23fq5/POWER%20BI%20IF%28PET1%29.pbix?dl=0

[BA_Pete · Accepted solution]

Hi @Anonymous ,

Please try this calculation in your custom column:

if List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})) then "NA"
else if [FRUIT LIST] = [FRUIT LIST_1] and
[AREA CODE] = [AREA CODE_2] and
[LANGUAGE] = [LANGUAGE_3] and
[SALES CODE] = [SALES CODE_4]
then "YES" else "NO"

Pete 

[Anonymous]

Hi Pete,

The new code not working. It's giving the result is "YES" but actual output required is "NA". Please refer the snapshot and file.

https://www.dropbox.com/s/68n3ypefwfnna54/ERROR3.png?dl=0

https://www.dropbox.com/s/3sp5fosy1k2ti88/POWER%20BI%20IF%28PET1%29.pbix?dl=0

[BA_Pete]

Hi @Anonymous ,

The first part of the calculation checks whether the entire row is blank in order to show "NA". Because you have the [STATUS(MANUAL)] column there it thinks that the row is not blank.

Remove your [STATUS(MANUAL)] column or move your new column creation step to a step above where you create your [STATUS(MANUAL)] column and it should work fine.

Pete

[Anonymous]

Hi Pete,

Yor are right. It's working fine now. Thanks for your support and help so far.

The last question is there any alternative way I can achive the same output?

something below like this ...https://community.powerbi.com/t5/Desktop/Evaluate-multiple-fields-for-blanks/td-p/27403

Please advise if not then I can close the thread.

Thank you.

[BA_Pete]

@Anonymous ,

Happy that's worked for you.

To be honest, there's probably a hundred ways to achieve what you want to do in Power BI, almost all of which would be as justified as my solution. You could certainly achieve the same output through both a DAX calculated column or a DAX measure, but I believe both these solutions would require more complicated calculations (although DAX isn't really my forte) and, more importantly, would put the calculation strain on the end user's system, whereas my Power Query solution offloads the work to Microsoft via the Power BI Service refresh process.

Pete

[Anonymous]

I will followup your advise. Thanks for your support and help so far. 

[harshnathani]

Hi @Anonymous ,

You can create a Column  in DAX 

Status1 = IF( IFS[FRUIT LIST] = IFS[FRUIT LIST_1] && IFS[LANGUAGE] = IFS[LANGUAGE_3] && IFS[SALES CODE] = IFS[SALES CODE_4] && IFS[AREA CODE] = IFS[AREA CODE_2] , "YES", "NO")

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

Hi,

Thanks for your reply. I try to apply the formula but I got the error message "Failed to resolve name ifs. It is not a vaild table, variable or Name".

How can I added IFS in Power BI. 

[Anonymous]

Also received the error "cannot find table "IFS".