Forum Discussion

marvelbites06's avatar
marvelbites06
Frequent Visitor
2 years ago
Solved

DAX for multiple Conditions: IF and OR

I have a situation Where I want to use 4 conditions for Calculate the Sum of the Qty. Below is the base DAX That I am using but i have the put filter on the Page to get the results. Now I want to compare 6 movement types and all refer to same Colun Quantity Nu but due to which I am not able to show the comparing both on the same visual. Please help to build the Dax.

 

Base DAX which Gives the results but need to put Filters on visual.

Blocked = VAR Result = SUMX(MATERIAL_MOVEMENT, VAR PosQty = IF(MATERIAL_MOVEMENT[QuantityNum] > 0 , MATERIAL_MOVEMENT[QuantityNum]) RETURN PosQty ) RETURN Result

 

 

 

Not giving the Results DAX:

 Blocked = VAR Result = SUMX(MATERIAL_MOVEMENT, VAR PosQty = IF(MATERIAL_MOVEMENT[QuantityNum] > 0 && (MATERIAL_MOVEMENT[MovementTypeCd] = "344" ||MATERIAL_MOVEMENT[MovementTypeCd] = "996" || MATERIAL_MOVEMENT[MovementTypeCd] = "Z44"), MATERIAL_MOVEMENT[QuantityNum]) RETURN PosQty ) RETURN Result

 

 

Blocked Qty is the Expected Result and Blocked is the new Dax after putting the Movement type condition to it. Similary for unblocked there are 3 movement types as 343, 995 and Z43.

Please let me know how to form the condition around it.

 

Jihwan_Kim 

GuyInACube 

dufoq3 

Need Help
Tips and Tricks
  • marvelbites06's avatar
    marvelbites06
    2 years ago

    Got the Solution. Thank You. Filtering Issue. 

     

    Blocked =
        VAR Result =
            SUMX(
                MATERIAL_MOVEMENT_FACT_G_VW,
                VAR PosQty =
                    IF(
                        AND(
                            MATERIAL_MOVEMENT[QuantityNum] > 0,
                            OR(
                                MATERIAL_MOVEMENT[MovementTypeCd] = "344",
                                OR(
                                    MATERIAL_MOVEMENT[MovementTypeCd] = "996",
                                    MATERIAL_MOVEMENT[MovementTypeCd] = "Z44"
                                )
                            )
                        ),
                        MATERIAL_MOVEMENT[QuantityNum],
                        0
                    )
                RETURN PosQty
            )
        RETURN Result

3 Replies

  • Kaviraj11's avatar
    Kaviraj11
    Icon for Solution Sage rankSolution Sage
    2 years ago

    Did you try with SWITCH?

     

    EXAMPLE:

    = SWITCH (
    TRUE(),
    [Reorder Point] > [Safety Stock Level], "Good: Safety stock level exceeded",
    [Reorder Point] = [Safety Stock Level], "Minimal: Safety stock level met",
    [Reorder Point] < [Safety Stock Level], "At risk: Safety stock level not met",
    ISBLANK ( [Reorder Point] ), "Incomplete: Reorder point not set",
    ISBLANK ( [Safety Stock Level] ), "Incomplete: Safety stock level not set",
    "Unknown"
    )

     

    SWITCH function (DAX) - DAX | Microsoft Learn

    • marvelbites06's avatar
      marvelbites06
      Frequent Visitor
      2 years ago

      Yes, Tried SWITCH but still the results are same.

      Blocked =
          VAR Result =
              SUMX(
                  MATERIAL_MOVEMENT_FACT_G_VW,
                  VAR PosQty =
                      SWITCH(
                          TRUE(),
                          MATERIAL_MOVEMENT_FACT_G_VW[QuantityNum] > 0 &&
                          MATERIAL_MOVEMENT_FACT_G_VW[MovementTypeCd] = "344",
                          MATERIAL_MOVEMENT_FACT_G_VW[MovementTypeCd] = "996",
                          MATERIAL_MOVEMENT_FACT_G_VW[MovementTypeCd] = "Z44",
                          MATERIAL_MOVEMENT_FACT_G_VW[QuantityNum],
                          BLANK()
                      )
                  RETURN PosQty
              )
          RETURN Result
       
  • marvelbites06's avatar
    marvelbites06
    Frequent Visitor
    2 years ago

    Got the Solution. Thank You. Filtering Issue. 

     

    Blocked =
        VAR Result =
            SUMX(
                MATERIAL_MOVEMENT_FACT_G_VW,
                VAR PosQty =
                    IF(
                        AND(
                            MATERIAL_MOVEMENT[QuantityNum] > 0,
                            OR(
                                MATERIAL_MOVEMENT[MovementTypeCd] = "344",
                                OR(
                                    MATERIAL_MOVEMENT[MovementTypeCd] = "996",
                                    MATERIAL_MOVEMENT[MovementTypeCd] = "Z44"
                                )
                            )
                        ),
                        MATERIAL_MOVEMENT[QuantityNum],
                        0
                    )
                RETURN PosQty
            )
        RETURN Result