Forum Discussion

don_writer's avatar
don_writer
Helper II
7 years ago
Solved

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 0...
Need Help
  • HotChilli's avatar
    HotChilli
    7 years ago

    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's avatar
    don_writer
    7 years ago

    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"))
don_writer's avatar
don_writer
Helper II
7 years ago

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

HotChilli's avatar
HotChilli
Community Champion
7 years ago

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's avatar
    don_writer
    Helper II
    7 years ago

    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's avatar
      HotChilli
      Community Champion
      7 years ago

      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's avatar
        don_writer
        Helper II
        7 years ago
        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.