Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello,
Im new with PBI. I'm having trouble with a function i wrote:
Pagamento Respectivo = SWITCH (
RESULTADO PONDERADO is decimal number .2
What I´m doing wrong??
Thank you in advance for your attention =]
Solved! Go to Solution.
I would start by testing a much simplied SWITCH statement:
My Switch Statement = SWITCH ( TRUE(), 1 = 1, "True", "False")
Then slowly add in ONE arguementat a time:
My Switch Statement = SWITCH ( TRUE(), 0 <= SUM(RVA[RESULTADO PONDERADO])/100, "True", "False")
I suspect that it is having trouble with this statement:
0 <= SUM(RVA[RESULTADO PONDERADO])/100 < 0.8
A couple of things about this:
Every time the statement encounters this portion: SUM(RVA[RESULTADO PONDERADO]) it needs to re-evaluate it, and you have it listed 17 times.
Better: Use a VAR (variable) to replace it:
My Switch Statement =
VAR MyVar = SUM(RVA[RESULTADO PONDERADO])/100
Return SWITCH ( TRUE(), 0 <= MyVar , "TRUE", "FALSE")
But in looking at the comparison you are doing, it is a double compare. I suspect the real issue is that you need each statement to be something like this:
0 <= SUM(RVA[RESULTADO PONDERADO])/100 && SUM(RVA[RESULTADO PONDERADO])/100 < 0.8
That brings the total number of times it needs to calculate this to 34.
Seriously, investigate the VAR and RETURN syntax in Power BI DAX calculations
Proud to be a Super User! | |
Hi @beatriz_sales ,
Using a range table is a more maintainable and scalable approach for handling range-based logic. Instead of hardcoding ranges within a lengthy DAX formula, you can define the ranges and corresponding values in a separate table, allowing for easier updates and improved readability.
First, create a table in Power BI (or your data source) with three columns: MinValue, MaxValue, and OutputValue.
MinValue | MaxValue | OutputValue |
0 | 0.8 | 0 |
0.8 | 0.9 | 0.5 |
0.9 | 0.95 | 0.75 |
0.95 | 0.96 | 0.95 |
0.96 | 0.97 | 0.96 |
0.97 | 0.98 | 0.97 |
0.98 | 0.99 | 0.98 |
0.99 | 1 | 0.99 |
1 | 1.01 | 1.01 |
1.01 | 1.02 | 1.02 |
1.02 | 1.03 | 1.03 |
1.03 | 1.04 | 1.04 |
1.04 | 1.05 | 1.05 |
1.05 | 1.1 | 1.1 |
1.1 | 1.2 | 1.2 |
1.2 | 999.99 | 1.5 |
This table should define the range of values (MinValue and MaxValue) and the corresponding output value (OutputValue). For example, if the value lies between 0.8 and 0.9, the output would be 0.5. This range table will act as the reference for your calculation.
Once the range table is imported into Power BI (e.g., named Ranges), you can write a DAX measure to perform a lookup based on the ranges. The following DAX formula calculates the result dynamically by identifying the appropriate range for the given value. It uses the FILTER function to find the matching range and the MAXX function to extract the corresponding output value:
Pagamento Respectivo =
VAR CurrentValue = SUM(RVA[RESULTADO PONDERADO]) / 100
RETURN
MAXX(
FILTER(
Ranges,
CurrentValue >= Ranges[MinValue] &&
CurrentValue < Ranges[MaxValue]
),
Ranges[OutputValue]
)
This formula calculates the CurrentValue by dividing the sum of RESULTADO PONDERADO by 100. It then filters the Ranges table to find the row where the CurrentValue falls between the MinValue and MaxValue. Once the matching range is found, the formula returns the corresponding OutputValue.
By using this approach, you can easily update or expand the ranges by modifying the Ranges table without touching the DAX formula. This method also makes the calculation more transparent and easier to debug. If you encounter any issues or need further assistance, let me know!
Best regards,
Hi @beatriz_sales ,
Using a range table is a more maintainable and scalable approach for handling range-based logic. Instead of hardcoding ranges within a lengthy DAX formula, you can define the ranges and corresponding values in a separate table, allowing for easier updates and improved readability.
First, create a table in Power BI (or your data source) with three columns: MinValue, MaxValue, and OutputValue.
MinValue | MaxValue | OutputValue |
0 | 0.8 | 0 |
0.8 | 0.9 | 0.5 |
0.9 | 0.95 | 0.75 |
0.95 | 0.96 | 0.95 |
0.96 | 0.97 | 0.96 |
0.97 | 0.98 | 0.97 |
0.98 | 0.99 | 0.98 |
0.99 | 1 | 0.99 |
1 | 1.01 | 1.01 |
1.01 | 1.02 | 1.02 |
1.02 | 1.03 | 1.03 |
1.03 | 1.04 | 1.04 |
1.04 | 1.05 | 1.05 |
1.05 | 1.1 | 1.1 |
1.1 | 1.2 | 1.2 |
1.2 | 999.99 | 1.5 |
This table should define the range of values (MinValue and MaxValue) and the corresponding output value (OutputValue). For example, if the value lies between 0.8 and 0.9, the output would be 0.5. This range table will act as the reference for your calculation.
Once the range table is imported into Power BI (e.g., named Ranges), you can write a DAX measure to perform a lookup based on the ranges. The following DAX formula calculates the result dynamically by identifying the appropriate range for the given value. It uses the FILTER function to find the matching range and the MAXX function to extract the corresponding output value:
Pagamento Respectivo =
VAR CurrentValue = SUM(RVA[RESULTADO PONDERADO]) / 100
RETURN
MAXX(
FILTER(
Ranges,
CurrentValue >= Ranges[MinValue] &&
CurrentValue < Ranges[MaxValue]
),
Ranges[OutputValue]
)
This formula calculates the CurrentValue by dividing the sum of RESULTADO PONDERADO by 100. It then filters the Ranges table to find the row where the CurrentValue falls between the MinValue and MaxValue. Once the matching range is found, the formula returns the corresponding OutputValue.
By using this approach, you can easily update or expand the ranges by modifying the Ranges table without touching the DAX formula. This method also makes the calculation more transparent and easier to debug. If you encounter any issues or need further assistance, let me know!
Best regards,
Thank you so much for your response, and your patience!
That's perfect. Problem solved.
Best regards
I would start by testing a much simplied SWITCH statement:
My Switch Statement = SWITCH ( TRUE(), 1 = 1, "True", "False")
Then slowly add in ONE arguementat a time:
My Switch Statement = SWITCH ( TRUE(), 0 <= SUM(RVA[RESULTADO PONDERADO])/100, "True", "False")
I suspect that it is having trouble with this statement:
0 <= SUM(RVA[RESULTADO PONDERADO])/100 < 0.8
A couple of things about this:
Every time the statement encounters this portion: SUM(RVA[RESULTADO PONDERADO]) it needs to re-evaluate it, and you have it listed 17 times.
Better: Use a VAR (variable) to replace it:
My Switch Statement =
VAR MyVar = SUM(RVA[RESULTADO PONDERADO])/100
Return SWITCH ( TRUE(), 0 <= MyVar , "TRUE", "FALSE")
But in looking at the comparison you are doing, it is a double compare. I suspect the real issue is that you need each statement to be something like this:
0 <= SUM(RVA[RESULTADO PONDERADO])/100 && SUM(RVA[RESULTADO PONDERADO])/100 < 0.8
That brings the total number of times it needs to calculate this to 34.
Seriously, investigate the VAR and RETURN syntax in Power BI DAX calculations
Proud to be a Super User! | |
Hello Todd!
Yeap, your suspicion was correct! Correcting the statements solved the problem.
0 <= SUM(RVA[RESULTADO PONDERADO])/100 && SUM(RVA[RESULTADO PONDERADO])/100 < 0.8
Thank you so much!
DataNinja777 gave me another great tip, helped me a lot.
Thank you so much for your time and patient with the newbies.
My best regards.
While I am always hoping that the original post authors select my answer as a solution, I must admit that the solution offered by @DataNinja777 is MUCH more elegant! I'm not proud, and sincerely hope you accept that as well, or in place of mine.
In the end, if you learned something about Power BI and a few DAX functions, the we can consider this as "mission accomplished".
Proud to be a Super User! | |
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
71 | |
70 | |
38 | |
26 | |
26 |
User | Count |
---|---|
100 | |
87 | |
45 | |
43 | |
35 |