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 Resou...
DAX
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 

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi san_bxl ,

       

      You calculation is based on your data model.  We need to know the relationship in your report and what your table looks like.
      Could you share a sample file with us and then show us a screenshot with the result you want? This will make it easier for us to find the solution.

       

      Best Regards,
      Rico Zhou

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.