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
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
jochendecraene
Helper V
2 years ago

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