cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aaron1225 Regular Visitor
Regular Visitor

Related Function Help

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

 

2018-01-16_9-49-34.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Related Function Help

@aaron1225

 

Try this calculated column

 

Column In OneSideTable =
CALCULATE ( FIRSTNONBLANK ( ManySideTable[TEXT Column], 1 ) )
5 REPLIES 5
Super User
Super User

Re: Related Function Help

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

Re: Related Function Help

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 Regular Visitor
Regular Visitor

Re: Related Function Help

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

Re: Related Function Help

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.

Highlighted
Super User
Super User

Re: Related Function Help

@aaron1225

 

Try this calculated column

 

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