Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
psembaj
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"

 

ScreenShot045.jpgScreenShot046.jpg

 

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] )

View solution in original post

v-jiascu-msft
Microsoft Employee
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]
    )
)

Compare-a-value-of-a-table-in-a-range-of-values-from-another-table

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.

View solution in original post

4 REPLIES 4
v-jiascu-msft
Microsoft Employee
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]
    )
)

Compare-a-value-of-a-table-in-a-range-of-values-from-another-table

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.

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:

ScreenShot052.jpgScreenShot053.jpg

 

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

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] )

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

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

October NL Carousel

Fabric Community Update - October 2024

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