Compare two rows from four columns and give 1 if they match, else 0

[Anonymous]

Hello community, I am trying to see if there is a difference between tow rows comparing four columns, and if there is no difference, show 0, else 1. I tried with the IF function, and I get an error saying that: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

Here is the formula:

Value Comparisson =

var _Table = FILTER(Readings,Readings[id_asset] = EARLIER(readings[id_asset])

&& Readings[Meter Readings Short] = EARLIER(Readings[Meter Readings Short])

&& Readings[taken_date] = EARLIER(Readings[taken_date])

&& Readings[value] = EARLIER(Readings[value]))

return

IF(ISBLANK(_Table),BLANK(), TRUE())

And a screenshot of the table.

So, if we have same asset, same Meter readings short, same date and same value (comparing the two rows), result 0. When the values from the comparisson are different (SI03, SI04, SI05), new column should show 1. Any suggestions? Thank you

[Ashish_Mathur]

Hi,

Write this calculated column formula

=calculate(countrows(readings),

FILTER(Readings,Readings[id_asset] = EARLIER(readings[id_asset])

&& Readings[Meter Readings Short] = EARLIER(Readings[Meter Readings Short])

&& Readings[taken_date] = EARLIER(Readings[taken_date])

&& Readings[value] = EARLIER(Readings[value])))

Hope this helps.

[Anonymous · Accepted solution]

Hi @Anonymous 

You can create a new column like :

Column = VAR a=FILTER('Table','Table'[id_asset]=EARLIER('Table'[id_asset])&&'Table'[Meter Readings Short]=EARLIER('Table'[Meter Readings Short])&&'Table'[taken_date]=EARLIER('Table'[taken_date])&&'Table'[value]=EARLIER('Table'[value]))
return IF(COUNTAX(a,[id_asset])>1,0,1)

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Yolo, thank you very much. It works as expected. Countax is the key! 

[FreemanZ]

To compare rows, you would need an index column, with which to locate and compare rows. check this

https://community.powerbi.com/t5/Desktop/Power-Query-Split-data-onto-individual-rows/td-p/1928351

EARLIER is indeed misleading here. EARLIER is to call the previous layer of row context, instead of the row context of the previous row.