cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## 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

2 ACCEPTED SOLUTIONS
Super User

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

Helper II

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 8
Super User

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

Helper II

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

Super User

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

Helper II

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?

Super User

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.

Helper II
```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?

Super User

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

Helper II

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.