Forum Discussion

jochendecraene's avatar
jochendecraene
Helper V
2 years ago

Related function search next matching value

Hi   I have to tables that are related. I want to do a search in the related table. Since the values in my facts table are averages, I don't have an exact match. So I want to search the first value...
Need Help
Show and Tell
amitchandak's avatar
amitchandak
Super User
2 years ago

jochendecraene , Create a new column in Fact

 

New column =
var _min = Maxx(filter(RelatedTable, RelatedTable[Item] = Fact[Item] && RelatedTable[Cost] <= Fact[Avg COst]), RelatedTable[Cost])
var _max = Maxx(filter(RelatedTable, RelatedTable[Item] = Fact[Item] && RelatedTable[Cost] > Fact[Avg COst]), RelatedTable[Cost])
return
if(_max -[Avg COst] > [Avg COst] - _min , _max, _min)

 

 

You can use relatedtable or related, but that dependents on the relationship

 

refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8