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

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