Forum Discussion

murphm6's avatar
murphm6
Helper II
3 years ago
Solved

Compare values across multiple rows with same ID to create flags

Hi everyone,   I'm running into a problem where I need to compare multiple rows in a table to see if a certain charge type is higher than another.    Here is what the data looks like: Essentially...
Need Help
  • jgeddes's avatar
    jgeddes
    3 years ago

    Try the adding the following column to your table. (Replace costTable with your table name.)

     

    Grtr Ocean =
    var _freightValue =
    // find Ocean Freight value for given short invoice
    CALCULATE(
        MAX(costTable[Cost]),
        ALLEXCEPT(costTable,costTable[Short Invoice]),
        costTable[Charge Type] = "Ocean Freight"
    )
    var _result =
    // test if cost is greater than Ocean Freight value for given short invoice
    IF(
        [Cost] > _freightValue && costTable[Charge Type] <> "Ocean Freight",
        1,
        0
    )
    Return
    _result
murphm6's avatar
murphm6
Helper II
3 years ago

This seems to be on the right track. However I just ran it and when filtering for values that have '1', some of the returned 'Short invoices' don't have any ocean freight costs. Is there a way to exclude short invoices that don't have any associated 'OCean freight' charge types?

jgeddes's avatar
jgeddes
Super User
3 years ago

Amend the IF test line to

[Cost] > _freightValue && costTable[Charge Type] <> "Ocean Freight" && NOT(ISBLANK(_freightValue))
  • murphm6's avatar
    murphm6
    Helper II
    3 years ago

    That worked, thanks!!