Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
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.
If possible to achieve my output through the conditional formatting (add column - - - conditional formatting)? Please advice.
@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
Proud to be a Datanaut!
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"
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
Proud to be a Datanaut!
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.
@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
Proud to be a Datanaut!
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"
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
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
Proud to be a Datanaut!
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
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
Proud to be a Datanaut!
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.
@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
Proud to be a Datanaut!
I will followup your advise. Thanks for your support and help so far.
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)
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.
Also received the error "cannot find table "IFS".
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
107 | |
70 | |
70 | |
43 |
User | Count |
---|---|
148 | |
106 | |
104 | |
89 | |
65 |