Forum Discussion
Comparing the cells and returning the result
Hi,
I have a dataset which is having two columns with "Yes" or "No" texts. I'd like to create a new column by comparing these two labels,
Ex: If either of the cells contains "Yes" then return the value "Yes" in the third column.
Happy to connect and provide more information.
Regards,
Prasanna Kumar K.
This operation can be done in both DAX and Power Query. If the Yes/No columns are coming directly out of your source data, then i'd suggesting completing this within Power Query. If the Yes/No columns are being calculated on the basis of your data model itself (DAX Calculated Columns), then you'll need to do this within DAX itself.
Power Query Solution:
Go into Edit Queries and find the query that relates to the table with the yes/no columns.
Click on the last step of the "Applied Steps" and go into the Ribbon and select 'Add Column' -> 'New Custom Column'
Here you can build a simple formula to do what you need. It should be something like:
if [Column1] = "Yes" or [Column2] = "Yes" then "Yes" else "No"
(Your column names will be different and the Yes/No could be either 0/1 or True/False. Update approprately.
Dax Solution:
Rightclick on the table containing the Yes/No columns in the field picker on the right hand side of the screen. Select 'New Column'
Put in a dax formula similar to this:
[Column3] = IF( [Column1] = "Yes" || [Column2] = "Yes", "Yes", "No" )
Again change the names/values to suit your data.
4 Replies
This operation can be done in both DAX and Power Query. If the Yes/No columns are coming directly out of your source data, then i'd suggesting completing this within Power Query. If the Yes/No columns are being calculated on the basis of your data model itself (DAX Calculated Columns), then you'll need to do this within DAX itself.
Power Query Solution:
Go into Edit Queries and find the query that relates to the table with the yes/no columns.
Click on the last step of the "Applied Steps" and go into the Ribbon and select 'Add Column' -> 'New Custom Column'
Here you can build a simple formula to do what you need. It should be something like:
if [Column1] = "Yes" or [Column2] = "Yes" then "Yes" else "No"
(Your column names will be different and the Yes/No could be either 0/1 or True/False. Update approprately.
Dax Solution:
Rightclick on the table containing the Yes/No columns in the field picker on the right hand side of the screen. Select 'New Column'
Put in a dax formula similar to this:
[Column3] = IF( [Column1] = "Yes" || [Column2] = "Yes", "Yes", "No" )
Again change the names/values to suit your data.
Hello,
I'd like to put one more que here. Let say, my requirement is to consider "yes"or "no" using two existed columns(which were created using LOOKUPVALUE formula from another table in the same dataset).
I need to create a new column with the values "yes" or "no", if any one column (columns mentioned above) contains "yes" then it should give me the result "yes".Ex: if both the columns conatains 'yes' then the result should be 'yes'
if one column contains 'yes' and another contains 'no' then the result should be 'yes'
if one column contains 'no' and another contains 'yes' then the result should be 'yes'
if both the columns conatains 'no' then the result should be 'no'
Is there any formula to create a column like this?