Forum Discussion
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
Try this calculated column
Column In OneSideTable = CALCULATE ( FIRSTNONBLANK ( ManySideTable[TEXT Column], 1 ) )
5 Replies
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 tableThe 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 : * )
Thank you for the help, what if I need to pull in a TEXT value?
