Forum Discussion
Compare different values in different rows
Hi,
I am trying to compare values from one row to values in other rows to determine if all the criteria is present. Here is a sample of my table:
| Rel | OT | ObjectID | RO | RelatedID |
| 003 | S | 024 | O | 125 |
| 008 | S | 024 | P | 024 |
| 003 | S | 111 | O | 087 |
| 008 | S | 111 | P | 017 |
| 003 | S | 138 | O | 081 |
| 008 | S | 138 | P | 138 |
| 003 | S | 149 | O | 502 |
| 008 | S | 149 | P | 458 |
| 003 | S | 154 | O | 049 |
| 008 | S | 154 | P | 337 |
| 012 | S | 154 | O | 049 |
| 003 | S | 164 | O | 249 |
| 008 | S | 164 | P | 164 |
| 003 | S | 176 | O | 086 |
| 008 | S | 176 | P | 700 |
| 003 | S | 178 | O | 128 |
| 008 | S | 178 | P | 178 |
| 003 | S | 191 | O | 727 |
| 008 | S | 191 | P | 191 |
| 012 | S | 191 | O | 727 |
| 003 | S | 231 | O | 060 |
| 008 | S | 231 | P | 231 |
| 003 | S | 237 | O | 541 |
I'm looking to make certain there is always and ObjectID with and RO of "O" and one with "P". And it's generally okay if there are multiple "O"s but should only be one "P" per ObjectID.
How might I go about that in DAX and/or Power Query?
Thanks,
~Don
If the sample provided is Table1, you could create a 2nd table
Table2 = SELECTCOLUMNS(FILTER(Table1, Table1[RO] = "P"), "ObjectID", Table1[ObjectID], "RO", Table1[RO])
You could do a DISTINCTCOUNT on ObjectID in Table2 to look for ObjectID with more than 1 P
Then add a calculated column to Table1
Is Matching P = VAR _PtoFind = LOOKUPVALUE ( 'Table2'[ObjectID], 'Table2'[ObjectID], Table1[ObjectID] ) RETURN IF (Table1[RO] = "P", "Skip", IF (_PtoFind <> BLANK (), "Y", "N" ) )This should return N if there is an O record without a matching P.
Note: The calculated column will error if there are more than 1 P records in Table2 for a specific ObjectID
I'll be damned, I thought the fourth parameter was a alternate result if the output was blank. Thanks for this.
Here is the final code for anyone looking for this kind of thing.
Matching P = VAR varPtoFind = LOOKUPVALUE( srcJRChief[ObjectID], srcJRChief[ObjectID], srcJRChief[ObjectID], srcJRChief[RO],"P") RETURN IF(srcJRChief[RO]="P","Skip", IF(varPtoFind<>BLANK(),"Y","N"))
8 Replies
If the sample provided is Table1, you could create a 2nd table
Table2 = SELECTCOLUMNS(FILTER(Table1, Table1[RO] = "P"), "ObjectID", Table1[ObjectID], "RO", Table1[RO])
You could do a DISTINCTCOUNT on ObjectID in Table2 to look for ObjectID with more than 1 P
Then add a calculated column to Table1
Is Matching P = VAR _PtoFind = LOOKUPVALUE ( 'Table2'[ObjectID], 'Table2'[ObjectID], Table1[ObjectID] ) RETURN IF (Table1[RO] = "P", "Skip", IF (_PtoFind <> BLANK (), "Y", "N" ) )This should return N if there is an O record without a matching P.
Note: The calculated column will error if there are more than 1 P records in Table2 for a specific ObjectID
This is awesome and the direction I was contemplating. Just curious if there is a way to accomplish this without an extra table?
There is. I started with the separate table to make testing easier.
I won't do it for you but it's not too different from the code I provided. Hint : you can use LOOKUPVALUE without a separate table