Return a specific value based on aggregated data and applying criteria

[Anonymous]

Hi,

How do I create a value that is based on multiple inputs/criteria in multiple rows and columns of data?

Example:

• Any given company has a single 'Global Company ID' (GCID), which may consist of multiple 'Local Company IDs' (LCID). In the example below I show two companies. 
• Each of those LCID's has a specific value (i.e. Cust of Prod1, Non Cust w/ Opty, etc...), hence can be described as a Customer with Yes or No by row - displayed in the 'Local Customer'-column. 
• From a 'GCID'-perspective, however, this would be conflicting data, because you can apply both Local Customer values, Yes's and No's, for the company with the GCID ABC123456

Desired outcome - see last column:

Which formula can I use to create a column that:

• for each GCID
• looks all the related LCID's
• searches for 'YES's in that 'Local Customer' column
• displays YES for each row of the 'Global Customer'-column - if it finds a single 'YES' in the 'Local Customer'-column, OR
• displays NO for each row of the 'Global Customer'-column - if it does NOT find a single 'YES' in the 'Local Customer'-column for a given GCID

Thanks for your help!

[Greg_Deckler · Accepted solution]

@Anonymous Maybe:

Measure = 
  VAR __Table = FILTER('Table',[Local Customer] = "Yes")
  VAR __Count = COUNTROWS(__Table) + 0
RETURN
  IF(__Count > 0, "YES", "NO")