Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
adraus
Frequent Visitor

Compare two fields to populate a new column

I want to do a simple thing...

I want to compare two columns on my report.  If they're the same, I want a new column that says "Yes".

(I'm trying to see if comments were entered by an employee or his supervisor).

 

This doesn't work:

Column = if([CreatedByUser] = RELATED('Workers(2)'[User]), "Yes")
 
The error is "The column 'Workers(2)[User]' either doesn't exist or doesn't have a relationship to any table available in the current context."
So what's the deal?  Do the two tables have to be directly linked?  I can't do that...
These snips show the two fields that I'm trying to compare:
 
adraus_0-1657903768956.png

 

adraus_1-1657903816607.png

 

adraus_2-1657904648555.png

 

1 ACCEPTED SOLUTION
adraus
Frequent Visitor

I very much appreciate the assistance from users on this forum!  I found that the solution for my situation was that I needed to do more in Power Query, rather than expecting to do column comparisons in the service.

 

I first did a 'merge queries', bringing the two userid fields into one merged table.  From there, I added a custom column using an IF statement, populating my new column with data from the existing 'comments' column only if the two userIDs were different.  If they were the same, I put "remove" into the new column, instead of the comments.  Then, in a next step, I filtered rows and removed all rows containing the string "remove".

 

I've got to remember that Power Query is where all the action is.

 

Many thanks!

View solution in original post

7 REPLIES 7
adraus
Frequent Visitor

I very much appreciate the assistance from users on this forum!  I found that the solution for my situation was that I needed to do more in Power Query, rather than expecting to do column comparisons in the service.

 

I first did a 'merge queries', bringing the two userid fields into one merged table.  From there, I added a custom column using an IF statement, populating my new column with data from the existing 'comments' column only if the two userIDs were different.  If they were the same, I put "remove" into the new column, instead of the comments.  Then, in a next step, I filtered rows and removed all rows containing the string "remove".

 

I've got to remember that Power Query is where all the action is.

 

Many thanks!

SpartaBI
Community Champion
Community Champion

I don't understand how LOOKUPVALUE would help.  I already have both fields on my report.  I just want to compare the two fields and, if they are the same, leave the new column blank.  If they are different, populate the new column.

 

adraus_0-1657916610376.png

 

vapid128
Solution Specialist
Solution Specialist

Measure:

isSame = 

IF(MAX(TableName[User])=MAX(TableName[CreateByUser]),"YES","NO")

I had hopes with the MAX function!  but everything is coming up "NO":

 

adraus_0-1657933130750.png

I did a merge of queries to get my two "user" fields into the same table.  In the end, I actually want to put the contents of a field from another table (comments) into my new column, rather than yes/no, based on whether the two users are the same.

vapid128
Solution Specialist
Solution Specialist

If those 2 are measures, try if(measure1=measure2)

Without max function.

 

 

ribisht17
Super User
Super User

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors