Compare values across multiple rows with same ID to create flags

[murphm6]

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, I want to look at each 'Short Invoice' and create a flag (simple 1,0) if there is a 'Cost' that is higher than the 'Ocean Freight' cost. How can I compare across multiple rows where they all have a unique ID?

[jgeddes · Accepted solution]

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]

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]

Amend the IF test line to

[Cost] > _freightValue && costTable[Charge Type] <> "Ocean Freight" && NOT(ISBLANK(_freightValue))

[murphm6]

That worked, thanks!!