Related Function Help

[aaron1225]

Normally I can work with RELATED but in this instance I can not get what I need.  Basically trying to compare two tables identify if what the overlap is.

Senerio:  have a table with opportunities and a table with leads, I want to identify on my opportunities table if the opportunity originated as a lead.  Identifier is the Opportunity ID and the relationship is one to many.  

Issue:  Realated function will pull in from opportunties table but the related fuction will not pull in on the Opportunities table

[Anonymous]

The Related Function can't pull in on the Opportunities table beacuse, RELATED fails to figure out which row value has to bring in as it was supllied with mutliple rows (many side relationship have mutliple rows for a given row in one side).

In order to deal with multiple rows we have to use RELATEDTABLE instead of RELATED and then you can aggregate on multiple rows to bring in one value for each row in Opportunities table.

RELATED is used in many side of a relationship in (* : 1)

RELATEDTABLE is used in one side of a relationship in (1 : * )

[aaron1225]

Thank you for the help, what if I need to pull in a TEXT value?

[Anonymous]

On what bases do you need to pull the Text value as you are supplying many text values in to the Function. We need a condition to sort out one text value out of multiple values supplied.

[Zubair_Muhammad · Accepted solution]

@aaron1225

Try this calculated column

Column In OneSideTable =
CALCULATE ( FIRSTNONBLANK ( ManySideTable[TEXT Column], 1 ) )

[Zubair_Muhammad]

hI @aaron1225

RELATED works only on the many side Table to pull a value from ONE SIDE TABLE

It is quite similar to VLOOKUP in excel

You can use Calculate(sum(ManysideTable[Column])) in the one side table to pull aggregated information from Many side

Or You can use Calculate plus filter combination to pull specific value from the many side table