Nested IF formula optimization

[malamin]

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 avail

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

[v-shex-msft]

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

[malamin]

@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,

[v-shex-msft]

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

[malamin]

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,

[v-shex-msft]

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