Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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])))))))))))))))))))))))))))))
Solved! Go to 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:
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
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
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)
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
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:
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
87 | |
82 | |
65 | |
63 | |
57 |
User | Count |
---|---|
171 | |
113 | |
110 | |
73 | |
73 |