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