Forum Discussion

jochendecraene's avatar
jochendecraene
Icon for Helper V rankHelper 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 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?

Need Help
Show and Tell

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper 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
      Icon for Helper V rankHelper 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.