cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
malamin Frequent Visitor
Frequent Visitor

Nested IF formula optimization

Dear Gurus,

I am trying to calculate an optimized demand for a set of products. The optimized demand depends on the count of locations that needs the product and under which area the location falls.

I am using the nested IF statment below below, but I keep getting blank value at the end. I have also tried SWITCH but to no availCapture.PNG

 

 

Optimized Demand = 
SUMX (
'ProductDemand',

IF ([Loc Count] = 0,0,

IF('ProductDemand'[Area] = "Area1" && 'ProductDemand'[Area] = "Area2", [Loc Count]*2, 
IF ('ProductDemand'[Area] = "Area3" && 'ProductDemand'[Area] = "Area4 && 'ProductDemand'[Area] = "Area5 && [Loc Count] > 0  && [Loc Count] < 3 ,

 [Loc Count] + 1,
IF (AND ( [Loc Count] >= 3, [Loc Count] <= 5 ),[Loc Count] + 2,
IF (AND ( [Loc Count] > 5, [Loc Count] <= 9 ),[Loc Count] + 3,

IF ('ProductDemand'[Area] = "Area6" && 'ProductDemand'[Area] = "Area7” && [Loc Count] > 0  && [Loc Count] < 4 ,  [Loc Count] + 1, [Loc Count] + 2))), Loc Count] + 4)))

 

 I am very new to DAX and power BI and I know that I am doing something wrong or that my approach is not working,

 

 

Thanks and sorry for the long post

5 REPLIES 5
Highlighted
Community Support Team
Community Support Team

Re: Nested IF formula optimization

HI @malamin,

 

After check on your formula, I haven't found any condition correspond to [Loc Count] +4, it seems wrong in your formula.

Optimized Demand =
VAR t = [Loc Count]
VAR condition =
    IF (
        t = 0,
        0,
        IF (
            'ProductDemand'[Area] IN { "Area1", "Area2" },
            t * 2,
            IF (
                'ProductDemand'[Area] IN { "Area3", "Area4", "Area5" }
                    && AND ( t > 0, t < 3 ),
                t + 1,
                IF (
                    AND ( t >= 3, t <= 5 ),
                    t + 2,
                    IF (
                        AND ( t > 5, t <= 9 ),
                        t + 3,
                        IF (
                            'ProductDemand'[Area] IN { "Area6", "Area7" }
                                && AND ( t > 0, t < 4 ),
                            t + 1,
                            t + 2
                        )
                    )
                )
            )
        )
    )
RETURN
    SUMX ( 'ProductDemand', condition )

 

BTW, since you are using complex nested condition formula in sum function to loop all rows, I think it will effect calculation performance.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
malamin Frequent Visitor
Frequent Visitor

Re: Nested IF formula optimization

@v-shex-msft Thanks for the Reply!

 

I knew something was wrong!, What I am trying to do is:

 

 Optimized Demand = SUM ( IF Loc. Count =0 THEN 0
IF Area 1 AND Area2 THEN Loc. Count *2

 

IF Area 3 AND Area4 AND Area4  AND 0<Loc Count<3 THEN Loc. Count +3 

AND IF 3<=Loc. Count<=5 THEN Loc.Count +2

AND IF 5<Loc. Count<=9 THEN Loc. Count +3 OTHERWISE Loc. Count +4

IF Area6 AND Area 7 AND 0<Loc.Count<4 THEN Loc.Count +1 OTHERWISE Loc.Count +2

 

That's basically it. 

 

Best Regards,

Community Support Team
Community Support Team

Re: Nested IF formula optimization

HI @malamin,

 

Obviously, it should more clarify than previous one, I modify my formula based on new conditions:

 Optimized Demand =
 VAR _LOC= LoC.Count
 RETURN
 SUMX('ProductDemand',
 IF( 'ProductDemand'[Area] IN { "Area1", "Area2" }, _LOC*2,
	IF('ProductDemand'[Area] IN { "Area3", "Area4", "Area5" },
		IF(_LOC>0&&_LOC<3,_LOC+3,
		IF(_LOC>=3&&_LOC<=5,_LOC+2,
		IF(_LOC>5&&_LOC<=9,_LOC+3,_LOC+4))),
	IF('ProductDemand'[Area] IN { "Area6", "Area7"},
		IF(_LOC>0&&_LOC<4,_LOC+1,_LOC+2))))
)

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
malamin Frequent Visitor
Frequent Visitor

Re: Nested IF formula optimization

Hi @v-shex-msft,

 

Much thanks for your help.

 

However I have the same calculation in an excel sheet (that I used normally use for this calculation) but the result from PBI is significantly larger, but only for the filters for Area3 to Area7 (Calculation for Area1 and Area2 brings the same result).

 

Apologies for the hassle, but any idea why it could be so?

 

Regards,

 

 

Community Support Team
Community Support Team

Re: Nested IF formula optimization

Hi @malamin,

 

I think these formula may not fully works in sumx function.

 

For your scenario, I'd like to suggest you if condition part formula to create new calculated column with replace loc count value, then summarize based on new column instead to direct summarize and replace current loc count in sumx function at same time.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |