Help with SWTCH function

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

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.

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,