Compare different values in different rows

[don_writer]

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

[HotChilli · Accepted solution]

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

[HotChilli · Accepted solution]

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

[don_writer]

This is awesome and the direction I was contemplating. Just curious if there is a way to accomplish this without an extra table?

[HotChilli]

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

[don_writer]

I appreciate the direction.

So I certainly get what the two table solution is doing. I know we can read off of virtual tables but I think that's the part I am missing, as in uncertain how I'd appraoch this. Is that what I should be trying to do here? Generate the table within the LOOKUPVALUE function?

[HotChilli]

You're probably overthinking this.

Let's look at this code

VAR _PtoFind =
    LOOKUPVALUE (
        'Table2'[ObjectID], 
        'Table2'[ObjectID], Table1[ObjectID]
    )

It says "Find me an ObjectID from Table2  (which is a table with only RO = 'P') and I'm going to pass the ObjectID of the current row " or, in other words, "Does a 'P' exist for the current row?"

You want to change this variable to check Table1 in a similar manner (because now  Table2 does not exist).  Therefore (next hint) you need to replace references to Table2 with references to Table1.  Also , you have to tell LOOKUPVALUE that Table1[RO] is P.

[don_writer]

Matching P = 
    VAR varPtoFind = 
        LOOKUPVALUE(
            srcJRChief[ObjectID],
            srcJRChief[ObjectID],
            CALCULATE(VALUES(srcJRChief[ObjectID]),FILTER(srcJRChief,srcJRChief[RO]="P")))
    RETURN
        IF(srcJRChief[RO]="P","Skip",
        IF(varPtoFind<>BLANK(),"Y","N"))

I was thinking something like this but I get an error. I am assuming because the LOOKUPVALUE search_value parameter says it can't take an expression in the same table. Is there a way around this other than building another table?

Thanks again for your help.

[HotChilli]

Way too complicated.  LOOKUPVALUE https://dax.guide/lookupvalue/

has syntax of the form: 

      thing i want,

      search_column, search_column_value

      2nd_search_column, 2nd_search_column_value

LOOKUPVALUE(
            srcJRChief[ObjectID],
            srcJRChief[ObjectID], srcJRChief[ObjectID],
            srcJRChief[RO],"P")

The syntax does look weird but it means - get me an ObjectID for the current row's ObjectID and the RO is P.

This syntax will fail if there is more than one P row per ObjectID

[don_writer · Accepted solution]

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"))