Is there any limitation with number of conditional if, in power BI?

[yaguaso]

I am trying to introduce 24 conditionals IF in a new column name “ADJUDICADO” (anex) from a Table name “COSTO_IND_ADJUD”, to give several values. The new column is referenced to two columns [Index] and [CANTIDAD_ADJUD]. The DAX expression works fine for 24 IF but only shows 17 results (from conditional 1 to # 17). No errors are show in the referenced DAX expression for the new column.

It is there any DAX expression to find the desired result (24 variables)? 

Thanks in advance

ADJUDICADO = IF([Index] = 0, IF([CANTIDAD_ADJUD] > 100, ([CANTIDAD_ADJUD]-100)*40+2000,  2000), IF([Index] = 1, [CANTIDAD_ADJUD], IF([Index] = 2, IF([CANTIDAD_ADJUD] > 100, ([CANTIDAD_ADJUD]-100)*25+1500, [CANTIDAD_ADJUD]), IF([Index] = 3, [CANTIDAD_ADJUD], IF([Index] = 4, [CANTIDAD_ADJUD], IF([Index] = 5 , IF([CANTIDAD_ADJUD] > 750, [CANTIDAD_ADJUD]*3.180789, 3081.900), IF([Index] = 6, [CANTIDAD_ADJUD],  IF([Index] = 7, [CANTIDAD_ADJUD]*9.422+28.9,  IF([Index] = 8, [CANTIDAD_ADJUD], IF([Index] = 9, [CANTIDAD_ADJUD], IF([Index] = 10,  IF([CANTIDAD_ADJUD] > 101, ([CANTIDAD_ADJUD])*2,  400), IF([Index] = 11, [CANTIDAD_ADJUD], IF([Index] = 12, [CANTIDAD_ADJUD]/2, IF([Index] = 13, [CANTIDAD_ADJUD]/1.5, IF([Index] = 14, [CANTIDAD_ADJUD]/6, IF([Index] = 15, [CANTIDAD_ADJUD]/0.41379306, IF([Index] = 16, [CANTIDAD_ADJUD]+1750, IF([Index] = 17, IF([CANTIDAD_ADJUD]<800, ([CANTIDAD_ADJUD]*0.05+20), IF([Index] = 18, IF([CANTIDAD_ADJUD]<850,([CANTIDAD_ADJUD]*0.176+50), IF([Index] = 19, [CANTIDAD_ADJUD], IF([Index] = 20, [CANTIDAD_ADJUD], IF([Index] = 21,  [CANTIDAD_ADJUD], IF([Index] = 22, IF([CANTIDAD_ADJUD]<850,([CANTIDAD_ADJUD]*3.81), IF([Index] = 23, IF([CANTIDAD_ADJUD]<100,([CANTIDAD_ADJUD]*18.675), IF([Index] = 24, [CANTIDAD_ADJUD])))))))))))))))))))))))))))))

[djurecicK2 · Accepted solution]

Hi @yaguaso ,

 I'm not aware of a set limit other then memory constraints.

In this case it seems like a Switch statement would be a better approach for readability and performance. Here is additional information:

https://www.phdata.io/blog/how-to-easily-write-a-multiple-if-statement-in-power-bi-using-the-switch-....

[yaguaso]

Hi, Ahmedx and djurecicK2

It works but not showing values in Indx 18, and indx 24. I check for a possible error of writing but everything is ok. I have annexed the results.

Thanks a lot for helping me

Orlando Y.

Valencia, Venezuela

CANTIDAD_ADJUD	Index	ADJUDICADO
154	0	4160
1500	1	1500
154	2	2850
650	3	650
1350	4	1350
1280	5	4071,40992
30	6	30
62	7	613,064
260	8	260
375	9	375
55	10	400
2180,82	11	2180,82
300	12	150
300	13	200
300	14	50
300	15	725,0000761
781	16	2531
528	17	46,4
781	18	
150	19	150
468	20	468
36	21	36
781	22	2975,61
66	23	1232,55
155	24	

[Ahmedx]

you need to break the measure into several parts
pls try this

ADJUDICADO =
VAR _MyResult =  [CANTIDAD_ADJUD]
VAR _Indx =[Index]
VAR _part1 = 
IF(_Indx = 0, 
IF(_MyResult > 100, (_MyResult-100)*40+2000,  2000), 
IF(_Indx = 1, _MyResult, IF(_Indx = 2, 
IF(_MyResult > 100, (_MyResult-100)*25+1500, _MyResult), 
IF(_Indx = 3, _MyResult, IF(_Indx = 4, _MyResult, 
IF(_Indx = 5 , IF(_MyResult > 750, _MyResult*3.180789, 3081.900)))))))
VAR _part2 = 
IF(_Indx = 6, _MyResult,  IF(_Indx = 7, _MyResult*9.422+28.9,  
IF(_Indx = 8, _MyResult, IF(_Indx = 9, _MyResult, 
IF(_Indx = 10,  IF(_MyResult > 101, (_MyResult)*2,  400))))))
VAR _part3 =  
IF(_Indx = 11, _MyResult, 
IF(_Indx = 12, _MyResult/2, 
IF(_Indx = 13, _MyResult/1.5, IF(_Indx = 14, _MyResult/6, 
IF(_Indx = 15, _MyResult/0.41379306, 
IF(_Indx = 16, _MyResult+1750))))))
VAR _part4 = 
IF(_Indx = 17, 
IF(_MyResult<800, (_MyResult*0.05+20), 
IF(_Indx = 18, 
IF(_MyResult<850,(_MyResult*0.176+50)))))
VAR _part5 =
IF(_Indx = 19, _MyResult, 
IF(_Indx = 20, _MyResult, 
IF(_Indx = 21,  _MyResult, 
IF(_Indx = 22, 
IF(_MyResult<850,(_MyResult*3.81))))))
VAR _part6 =
IF(_Indx = 23, 
IF(_MyResult<100,(_MyResult*18.675), 
IF(_Indx = 24, _MyResult)))
RETURN
 SWITCH( TRUE(),
   _Indx<=5,_part1,
   _Indx<=10,_part2,
   _Indx<=16,_part3,
   _Indx<=18,_part4,
   _Indx<=22,_part5,
   _Indx<=24,_part6)

[yaguaso · Accepted solution]

Hi, Ahmedx and djurecicK2

It works but not showing values in Indx 18, and indx 24. I check for a possible error of writing but everything is ok. I have annexed the results.

Thanks a lot for helping me

Orlando Yaguas

Valencia, Venezuela

CANTIDAD_ADJUD	Index	ADJUDICADO
154,00	0,00	4160,00
1500,00	1,00	1500,00
154,00	2,00	2850,00
650,00	3,00	650,00
1350,00	4,00	1350,00
1280,00	5,00	4071,41
30,00	6,00	30,00
62,00	7,00	613,06
260,00	8,00	260,00
375,00	9,00	375,00
55,00	10,00	400,00
2180,82	11,00	2180,82
300,00	12,00	150,00
300,00	13,00	200,00
300,00	14,00	50,00
300,00	15,00	725,00
781,00	16,00	2531,00
528,00	17,00	46,40
781,00	18,00	
150,00	19,00	150,00
468,00	20,00	468,00
36,00	21,00	36,00
781,00	22,00	2975,61
66,00	23,00	1232,55
155,00	24,00	

[djurecicK2 · Accepted solution]

Hi @yaguaso ,

 I'm not aware of a set limit other then memory constraints.

In this case it seems like a Switch statement would be a better approach for readability and performance. Here is additional information:

https://www.phdata.io/blog/how-to-easily-write-a-multiple-if-statement-in-power-bi-using-the-switch-....