Forum Discussion

tfmoraes's avatar
tfmoraes
New Member
3 years ago

Join based on a range of values

Hi,

 

I am trying to find a way to join 2 tables so that the area is the identifier, but I want a inner join based on a parameter that sets a range of values:

 

 

In this example, say the parameter is 3% of Area1. So the cells in Area2 that are within +-3% of Area1 will match.

I am not really sure if a join is a feasible solution, or even a clean one.

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi tfmoraes ,

     

    First create a relationship.

    Then you can create the following calculated column.

    Output = 
var _area2=RELATED('Table 2'[Area2])
var _max=[Area1]*1.03
var _min=[Area1]*0.97
return IF(_area2>=_min&&_area2<=_max,[Area1])

    According to the logic you provided, the matching range of city A is 9.7-10.3, and 10.8 is not in this range.

    You can also create a meausre to return the output.

    Output1 = 
var _area1=SUM('Table 1'[Area1])
var _area2=SUM('Table 2'[Area2])
var _max=_area1*1.03
var _min=_area1*0.97
return IF(_area2>=_min&&_area2<=_max,_area1)

     

     

    Best Regards,

    Stephen Tao

     

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

     

  • tfmoraes's avatar
    tfmoraes
    New Member
    3 years ago

    Hi Anonymous , thank you for chiming in!

     

    About the calculation, I thought about 30% and wrote 3% ğŸ˜².

     

    I'll surely try it on PowerBI. Can you tell me if I can do it using excel + Power Query?