Related function search next matching value

[jochendecraene]

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 in the related table that matches the average. 

So in the example, I want to search the value in the related table that best matches my average cost

I did some  searching but can't find the solution. Can someone have a look?

[amitchandak]

@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

[jochendecraene]

@amitchandak 

thnx for the help.

now, when I create this column I get an a value > 117.000

Maybe I was not clear in my question, sorry.

What I need is the corresponding scale from the related table. So in this example the avg of 79.795,10 in my facts table needs to match the first higher value in my related table, wich is 80.316,37 and than give my the corresponding scale A1 19.

I have a many to one relations on the scale item.

[Anonymous]

Hi @jochendecraene ,

I create a table as you mentioned.

Then I create two measures.

Average = AVERAGE('Table'[Count])

Closest Match =
VAR AverageCost = 'Table'[Average]
VAR MinDifference =
    MINX ( RELATEDTABLE ( 'Table' ), ABS ( 'Table'[Count] - AverageCost ) )
VAR _Count =
    CALCULATE (
        MIN ( 'Table'[Count] ),
        FILTER (
            RELATEDTABLE ( 'Table' ),
            ABS ( 'Table'[Count] - AverageCost ) = MinDifference
        )
    )
RETURN
    _Count

Finally you will get what you want.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jochendecraene]

@Anonymous 

thx, bu I'm not sure what to do. I'll explain better the scenario and what I need.

This is my simplified scenario:

- a facts table with the cost of employees with the corresponding scale and scale-detail

- a table with al scales, scale-details and corresponding cost

- a summerized table of my facts table by scale and the cost FTE by scale

I need the corresponding scale-detail in the summarized table out of the related scales table based on the best match between "cost FTE by scale" and "cost".

So for this scenario I need A1 20 in my summarized table