Help with SWTCH function

[beatriz_sales]

Hello,

Im new with PBI. I'm having trouble with a function i wrote:

Pagamento Respectivo = SWITCH (  

    TRUE(),

    0 <= SUM(RVA[RESULTADO PONDERADO])/100 < 0.8,0.0,

    0.8 <= SUM(RVA[RESULTADO PONDERADO])/100 < 0.9,0.5,

    0.9 <= SUM(RVA[RESULTADO PONDERADO])/100 < 0.95,0.75,

    0.95 <= SUM(RVA[RESULTADO PONDERADO])/100 < 0.96,0.95,

    0.96 <= SUM(RVA[RESULTADO PONDERADO])/100 < 0.97,0.96,

    0.97 <= SUM(RVA[RESULTADO PONDERADO])/100 < 0.98,0.97,

    0.98 <= SUM(RVA[RESULTADO PONDERADO])/100 < 0.99,0.98,

    0.99 <= SUM(RVA[RESULTADO PONDERADO])/100 < 1,0.99,

    SUM(RVA[RESULTADO PONDERADO])/100 = 1.0,1.0,

    1 < SUM(RVA[RESULTADO PONDERADO])/100 <= 1.01,1.01,

    1.01 < SUM(RVA[RESULTADO PONDERADO])/100 <= 1.02,1.02,

    1.02 < SUM(RVA[RESULTADO PONDERADO])/100 <= 1.03,1.03,

    1.03 < SUM(RVA[RESULTADO PONDERADO])/100 <= 1.04,1.04,

    1.04 < SUM(RVA[RESULTADO PONDERADO])/100 <= 1.05,1.05,

    1.05 < SUM(RVA[RESULTADO PONDERADO])/100 <= 1.1,1.1,

    1.1 < SUM(RVA[RESULTADO PONDERADO])/100 <= 1.2,1.2,

    SUM(RVA[RESULTADO PONDERADO])/100 > 1.20 ,1.50,

        "Unknown")

Im getting this error message:

 

RESULTADO PONDERADO is  decimal number .2

What I´m doing wrong??

Thank you in advance for your attention =]

[ToddChitt · Accepted 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

[DataNinja777 · Accepted solution]

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,

[beatriz_sales]

Thank you so much for your response, and your patience!

That's perfect. Problem solved. 

Best regards

[ToddChitt · Accepted 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

[beatriz_sales]

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.

[ToddChitt]

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".