Multiple OR condtions within calculate

[san_bxl]

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

[Poojara_D12]

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]

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

[v-rzhou-msft]

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.

[Poojara_D12]

@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 

[johnt75]

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