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
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?
Solved! Go to Solution.
What you need is the LOOKUP Function. Try this:
ValueNeeded = LOOKUPVALUE ( Table1[Incremento], Table1[Desde], Table2[Cant_Real] )
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
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
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
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
What you need is the LOOKUP Function. Try this:
ValueNeeded = LOOKUPVALUE ( Table1[Incremento], Table1[Desde], Table2[Cant_Real] )
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |