cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Compare a value of a table in a range of values ​​from another table

Hello! 1 query with two tables. Table 1 contains 6 columns: Fecha - Mes - Anio - Desde - Hasta - Incremento Table 2 contains 6 columns. The relationship between these tables is the Mes and Anio. I need each value of the table 2 column " Cant_Real" to be validated by table 1 between the "Desde" - "Hasta" range and return the value of the "Incremento".

Example:

Table 2: "Cant_Real" = 105            //     Corresponds:      Table 1: "Desde"=105    "Hasta"=109     //  Value that I need is: "110"

The values ​​in the column "DESDE" and "HASTA" change, so they can not be static. I need to evaluate a value between a range of values ​​and bring the value that corresponds to it from another column. I do not know how to do it. Could anyone help me?

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

What you need is the LOOKUP Function. Try this:

```ValueNeeded =
LOOKUPVALUE ( Table1[Incremento], Table1[Desde], Table2[Cant_Real] )```
Microsoft Employee

Hi @psembaj,

Please try this formula in a calculated column.

```Value =
CALCULATE (
MIN ( 'Table1'[Incremento] ),
FILTER (
'Table1',
'Table1'[DESDE] <= [Cant Real]
&& IF ( ISBLANK ( 'Table1'[HASTA] ), 99999, 'Table1'[HASTA] ) >= [Cant Real]
)
)
```

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Microsoft Employee

Hi @psembaj,

Please try this formula in a calculated column.

```Value =
CALCULATE (
MIN ( 'Table1'[Incremento] ),
FILTER (
'Table1',
'Table1'[DESDE] <= [Cant Real]
&& IF ( ISBLANK ( 'Table1'[HASTA] ), 99999, 'Table1'[HASTA] ) >= [Cant Real]
)
)
```

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

Hello! Good day! I was testing the proposed solution, but I find that it does not work correctly. For some reason it works with certain values ​​and not with others, example:

The values ​​for the month of 10 do it correctly, but not for those of the month 9. Any suggestions, ideas, comments? I thank you because I really do not know what is happening.

Thanks

Microsoft Employee

Hi @psembaj,

Just see from your snapshots, there isn't any data of month 9 in table 1. That's why the results are blanks. If this isn't the cause, please share a sample file. Please mask the sensitive parts first.

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

What you need is the LOOKUP Function. Try this:

```ValueNeeded =
LOOKUPVALUE ( Table1[Incremento], Table1[Desde], Table2[Cant_Real] )```

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors