topic Re: Compare two fields to populate a new column in DAX Commands and Tips

Compare two fields to populate a new column

Anonymous — Fri, 15 Jul 2022 17:08:47 GMT

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:

Re: Compare two fields to populate a new column

ribisht17 — Fri, 15 Jul 2022 17:21:34 GMT

Anonymous

Please check if it helps Solved: The column x either doesn't exist or doesn't have ... - Microsoft Power BI Community

Regards,

Ritesh

Re: Compare two fields to populate a new column

SpartaBI — Fri, 15 Jul 2022 17:21:59 GMT

Anonymous you can use LOOKUPVALUE:
https://www.youtube.com/watch?v=XTkrNdU6jWs

Showcase Report – Contoso By SpartaBI

Re: Compare two fields to populate a new column

Anonymous — Fri, 15 Jul 2022 20:24:48 GMT

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.

Re: Compare two fields to populate a new column

vapid128 — Fri, 15 Jul 2022 21:52:04 GMT

Measure:

isSame =

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

Re: Compare two fields to populate a new column

Anonymous — Sat, 16 Jul 2022 01:59:27 GMT

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

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.

Re: Compare two fields to populate a new column

vapid128 — Sat, 16 Jul 2022 02:13:27 GMT

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

Without max function.

Re: Compare two fields to populate a new column

Anonymous — Mon, 18 Jul 2022 19:34:34 GMT

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!