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

  • HotChilli's avatar
    HotChilli
    Community Champion
    7 years ago

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