Forum Discussion

san_bxl's avatar
san_bxl
New Member
1 year ago

Multiple OR condtions within calculate

I have to write a DAX which filters data from three connected tables.
table 1. Project Resource Assignemnet
table 2.Project Resource
table 3,Project_activity

I need to now get a sum from Resource Assignement  and have multiple conditons 1 ,2 and 3
Between these i need to have OR and Condition 2 and 3 i need a AND within it

M11-2a_Scope =
CALCULATE(
    SUM('Project-Resource Assignment'[PlannedUnits]),
    ALL('DATE TABLE'),
   
    -- condition 1
    'Project-Resource'[Id] IN {
        "C3-GAD-14",  
        "C3-GAD-12",  
        "C3-GAD-13",  
        "C3-GAD-11",  
        "C3-GAD-2",  
        "C3-GAD-1",  
        "C3-GAD-7",  
        "C3-M-9"    
    } ||

-- condition 2
    (
        'Project-Resource'[Id] = "C3-M-102" &&
        LEFT('Project-Activity'[Id], 14) = "CM-"
    ) ||

    --
-- condition 2
    (
        'Project-Resource'[Id] = "C3-M-88" &&
        LEFT('Project-Activity'[Id], 14) = "CM-S3"  
    )
)


## the above code doesnt work

DAX

5 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    1 year ago

    In both LEFT functions you are taking 14 characters, I think you meant to only take 4.

  • Poojara_D12's avatar
    Poojara_D12
    Icon for Super User rankSuper User
    1 year ago

    Hi san_bxl 

    Your DAX formula has logical flaws that prevent the CALCULATE function from interpreting the conditions as intended. Specifically, the logical OR (||) and AND (&&) operations must be properly nested, and the filter syntax needs to conform to the DAX filter context structure.

     

    Here’s the corrected DAX formula:

     

    M11-2a_Scope =
CALCULATE(
    SUM('Project-Resource Assignment'[PlannedUnits]),
    ALL('DATE TABLE'),
    
    // Condition 1
    OR(
        'Project-Resource'[Id] IN {
            "C3-GAD-14",  
            "C3-GAD-12",  
            "C3-GAD-13",  
            "C3-GAD-11",  
            "C3-GAD-2",  
            "C3-GAD-1",  
            "C3-GAD-7",  
            "C3-M-9"
        },

        // Condition 2 AND Condition 3
        AND(
            'Project-Resource'[Id] = "C3-M-102",
            LEFT('Project-Activity'[Id], 14) = "CM-"
        ),

        AND(
            'Project-Resource'[Id] = "C3-M-88",
            LEFT('Project-Activity'[Id], 14) = "CM-S3"
        )
    )
)

     

    This formula should now calculate the sum of PlannedUnits with the specified conditions applied correctly.

     

    Did I answer your question? Mark my post as a solution, this will help others!
    If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

    Kind Regards,
    Poojara
    Data Analyst | MSBI Developer | Power BI Consultant
    Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

     

    • San_Raz's avatar
      San_Raz
      Frequent Visitor
      1 year ago

      I am looking for
      condition 1 or Condition 2 or Conditon 3
      If it satisfies any, its should calculate

      • Poojara_D12's avatar
        Poojara_D12
        Icon for Super User rankSuper User
        1 year ago

        San_Raz 

        Can you try this dax:

        M11-2a_Scope =
CALCULATE(
    SUM('Project-Resource Assignment'[PlannedUnits]),
    ALL('DATE TABLE'),
    
    // Combine Condition 1, 2, and 3 with OR logic
    OR(
        // Condition 1
        'Project-Resource'[Id] IN {
            "C3-GAD-14",  
            "C3-GAD-12",  
            "C3-GAD-13",  
            "C3-GAD-11",  
            "C3-GAD-2",  
            "C3-GAD-1",  
            "C3-GAD-7",  
            "C3-M-9"
        },

        // Condition 2
        AND(
            'Project-Resource'[Id] = "C3-M-102",
            LEFT('Project-Activity'[Id], 14) = "CM-"
        ),

        // Condition 3
        AND(
            'Project-Resource'[Id] = "C3-M-88",
            LEFT('Project-Activity'[Id], 14) = "CM-S3"
        )
    )
)

         

        Did I answer your question? Mark my post as a solution, this will help others!
        If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

        Kind Regards,
        Poojara
        Data Analyst | MSBI Developer | Power BI Consultant
        Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS