Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Sum a Measure with Multiple If statements

Howdy!

 

I am attempting to SUM a measure that contains multiple IF statements but it keeps erroring out and I am at a loss as to why. I tried using the syntax found here who had a very similar problem to the one I am facing but I am unable to get it to work

 

My end goal is to the sum the total number of pallets to transfer from one warehouse to another and then display that total in a card visual. 

 

Here is the DAX of the measure that successfully displays the number of pallets to be transferred:

DC-LOU Pallet Transfer Qty =
IF(
   OR([Forecasted Days of supply REN]<=10, [Forecasted Days of supply LOU]>=45),
   0,
      IF(
      AND([Forecasted Days of supply REN]<=10, [Forecasted Days of supply LOU]<=45),
      .5,
         IF(
         AND([Forecasted Days of supply REN]>=45, [Forecasted Days of supply LOU]>=45),
         0,
            IF(
            AND([Forecasted Days of supply REN]>=45, [Forecasted Days of supply LOU]<=45),
            1,
               IF(AND([Forecasted Days of supply REN]<=30,[Forecasted Days of supply LOU]<=30),
               0,
               .5
               )
            )
         )
      )
)

 

Here is the DAX of the meausure that unsuccessfully totals the number of pallets from the above measure:

DC-LOU Pallet Transfer Sum =
SUMX ('Sales order data',
 IF(
    OR([Forecasted Days of supply REN]<=10, [Forecasted Days of supply LOU]>=45),
    0,
       IF(
       AND([Forecasted Days of supply REN]<=10, [Forecasted Days of supply LOU]<=45),
       .5,
          IF(
          AND([Forecasted Days of supply REN]>=45, [Forecasted Days of supply LOU]>=45),
          0,
             IF(
             AND([Forecasted Days of supply REN]>=45, [Forecasted Days of supply LOU]<=45),
             1,
                IF(AND([Forecasted Days of supply REN]<=30,[Forecasted Days of supply LOU]<=30),
                0,
                .5
                )
             )
          )
       )
 )
)

 

Here is a visual from PBi on what happens:

 

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , You need share the error details as formula seems fine

     

    Try like

     

    SUMX (values('Sales order data'[item code]),
    Switch( True() ,
    OR([Forecasted Days of supply REN]<=10, [Forecasted Days of supply LOU]>=45), 0,
    AND([Forecasted Days of supply REN]<=10, [Forecasted Days of supply LOU]<=45),.5,
    AND([Forecasted Days of supply REN]>=45, [Forecasted Days of supply LOU]>=45),0,
    AND([Forecasted Days of supply REN]>=45, [Forecasted Days of supply LOU]<=45),1,
    AND([Forecasted Days of supply REN]<=30,[Forecasted Days of supply LOU]<=30),0,
    .5
    )
    )

     

    or

     

     

    SUMX (summarize('Sales order data', 'Sales order data'[item code], , 'Sales order data'[ABC final], "_1",
    Switch( True() ,
    OR([Forecasted Days of supply REN]<=10, [Forecasted Days of supply LOU]>=45), 0,
    AND([Forecasted Days of supply REN]<=10, [Forecasted Days of supply LOU]<=45),.5,
    AND([Forecasted Days of supply REN]>=45, [Forecasted Days of supply LOU]>=45),0,
    AND([Forecasted Days of supply REN]>=45, [Forecasted Days of supply LOU]<=45),1,
    AND([Forecasted Days of supply REN]<=30,[Forecasted Days of supply LOU]<=30),0,
    .5
    )
    ), [_1])

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , You need share the error details as formula seems fine

     

    Try like

     

    SUMX (values('Sales order data'[item code]),
    Switch( True() ,
    OR([Forecasted Days of supply REN]<=10, [Forecasted Days of supply LOU]>=45), 0,
    AND([Forecasted Days of supply REN]<=10, [Forecasted Days of supply LOU]<=45),.5,
    AND([Forecasted Days of supply REN]>=45, [Forecasted Days of supply LOU]>=45),0,
    AND([Forecasted Days of supply REN]>=45, [Forecasted Days of supply LOU]<=45),1,
    AND([Forecasted Days of supply REN]<=30,[Forecasted Days of supply LOU]<=30),0,
    .5
    )
    )

     

    or

     

     

    SUMX (summarize('Sales order data', 'Sales order data'[item code], , 'Sales order data'[ABC final], "_1",
    Switch( True() ,
    OR([Forecasted Days of supply REN]<=10, [Forecasted Days of supply LOU]>=45), 0,
    AND([Forecasted Days of supply REN]<=10, [Forecasted Days of supply LOU]<=45),.5,
    AND([Forecasted Days of supply REN]>=45, [Forecasted Days of supply LOU]>=45),0,
    AND([Forecasted Days of supply REN]>=45, [Forecasted Days of supply LOU]<=45),1,
    AND([Forecasted Days of supply REN]<=30,[Forecasted Days of supply LOU]<=30),0,
    .5
    )
    ), [_1])

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Holy Meatballs of Moses, you're a genius! Thank you!

      I took the summarize DAX you built and made a slight alteration to the grouping and it worked perfectly! 

      DC-LOU Pallet Transfer Sum =
      ROUND(SUMX (summarize('Sales order data', 'Item lookup table'[Item Code], "_1",
      Switch( True() ,
      [Forecasted Days of supply REN]<=10, 0,
      [Forecasted Days of supply LOU]>=45, 0,
      AND([Forecasted Days of supply REN]<=30, [Forecasted Days of supply LOU]<=30), 0,
      AND([Forecasted Days of supply REN]<=45, [Forecasted Days of supply LOU]<=45), .5,
      AND([Forecasted Days of supply REN]>=45, [Forecasted Days of supply LOU]<=45), 1
      )
      ), [_1])*.9,1)