Compare values from 2 rows

[gauravnarchal]

Need help!!  How can I compare the data from two rows in a table?

I need to match “UnqNumber” field to find out missing "UnqNumber" in "SubmitTo" (2) of each Invoice.

[ToddChitt · Accepted solution]

One 'brute force' approach would be to seperate the base table into two in Power Query, each filtered respectively for the 1 or 2, then LEFT OUTER JOIN them together on Unique ID and look for nulls.

Another approach would be to create a measure that is the MAX(SubmitTo) and when you view the list by UniqueID, look for any where the MAX is 1 instead of 2.

I'm sure there are more ways.

[v-lionel-msft · Accepted solution]

Hi @gauravnarchal ,

Please refer to my .pbix file.

Best regards,
Lionel Chen

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

[amitchandak]

@gauravnarchal , The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

[gauravnarchal]

@amitchandak  - Each “UnqNumber” should exist twice (One as SubmitTo 1 & and other as SubmitTo 2) of each invoice no.

If it does not exist then it should return the Invoice as a "Missing".

So I wanted the measure to match/check "UnqNumber" of each Invoice with SubmitTo 1 & 2 and validate the missing.

I hope this makes some sense.

[ToddChitt · Accepted solution]

One 'brute force' approach would be to seperate the base table into two in Power Query, each filtered respectively for the 1 or 2, then LEFT OUTER JOIN them together on Unique ID and look for nulls.

Another approach would be to create a measure that is the MAX(SubmitTo) and when you view the list by UniqueID, look for any where the MAX is 1 instead of 2.

I'm sure there are more ways.