Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Try this calculated column
Column In OneSideTable = CALCULATE ( FIRSTNONBLANK ( ManySideTable[TEXT Column], 1 ) )
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 : * )
Thank you for the help, what if I need to pull in a TEXT value?
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.
Try this calculated column
Column In OneSideTable = CALCULATE ( FIRSTNONBLANK ( ManySideTable[TEXT Column], 1 ) )
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
97 | |
80 | |
67 | |
63 |
User | Count |
---|---|
146 | |
110 | |
107 | |
86 | |
63 |