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

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'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