DAX for calculate function with More than one OR Condition between each expressions.

[San_Raz]

I have a to write a DAX to find the sum of a field from a column by filtering codnitions withe respect to two other  connected tables
table 1: Project-Resource Assignment
table 2: Project-Resource
table 3:Project-Resource

There are three conditons but between each condition i=i need to have a "OR" operation.

M11-2a:_Scope =
CALCULATE(
SUM('Project-Resource Assignment'[PlannedUnits]),
ALL('DATE TABLE'),

-- Condition 1
'Project-Resource'[Id] IN {
"C3-14", -- Foundations
"C3-12", -- Pile
"C3-13", -- Pile Cap
"C3-11", -- Shinso Pile
"C3-2", -- Piers
"C3-1", -- Pier Cap
"C3-7", -- Steel Superstructure Erection
"C3-9", -- Superstructure Launching Bal CL
"C3--102", -- Well Foundation
"C3-88" -- Superstructure Casting
}
||

-- -- Condition 2
(
'Project-Resource'[Id] = "C3102" &&
LEFT('Project-Activity'[Id], 14) = "C3-S1b.2.1"
)

||

-- Condition 3
(
'Project-Resource'[Id] = "C3-M-88" &&
LEFT('Project-Activity'[Id], 13) = "C3-S3.3.7"
)
)


The above DAX will not work ,
how to achieve the above operations

[Anonymous]

HI @San_Raz,

Any update on this? Does these expressions work on your side? If not help, please provide more detailed information and description help us can clarify these and test.

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @San_Raz ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[DataNinja777 · Accepted solution]

Hi @San_Raz ,

To achieve the sum of PlannedUnits from the Project-Resource Assignment table with the filtering conditions applied across two connected tables (Project-Resource and Project-Activity), you need to ensure that the conditions are correctly applied within the CALCULATE function. The issue in your original formula is that you cannot directly use || (OR operator) to chain multiple conditions inside CALCULATE. Instead, you need to handle the "OR" logic using either the OR() function or separate FILTER functions for each condition.

Here is the corrected version of your DAX formula:

M11_2a_Scope =
CALCULATE(
    SUM('Project-Resource Assignment'[PlannedUnits]),
    ALL('DATE TABLE'),
    
    -- Condition 1
    FILTER(
        'Project-Resource',
        'Project-Resource'[Id] IN {
            "C3-14", "C3-12", "C3-13", "C3-11",
            "C3-2", "C3-1", "C3-7", "C3-9", "C3--102", "C3-88"
        }
    ),

    -- OR Condition
    OR(
        -- Condition 2
        FILTER(
            'Project-Resource',
            'Project-Resource'[Id] = "C3102" &&
            LEFT('Project-Activity'[Id], 14) = "C3-S1b.2.1"
        ),

        -- Condition 3
        FILTER(
            'Project-Resource',
            'Project-Resource'[Id] = "C3-M-88" &&
            LEFT('Project-Activity'[Id], 13) = "C3-S3.3.7"
        )
    )
)

This formula uses FILTER to apply each condition separately and combines Conditions 2 and 3 using the OR() function. It ensures that the sum of PlannedUnits is calculated for rows that match any of the conditions across the connected tables. The ALL('DATE TABLE') ensures that the date context is ignored when calculating the sum.

Alternatively, if you prefer to use a single FILTER block instead of the OR() function, you can combine all the conditions using the || operator inside the FILTER expression, as shown below:

M11_2a_Scope =
CALCULATE(
    SUM('Project-Resource Assignment'[PlannedUnits]),
    ALL('DATE TABLE'),
    FILTER(
        'Project-Resource',
        'Project-Resource'[Id] IN {
            "C3-14", "C3-12", "C3-13", "C3-11",
            "C3-2", "C3-1", "C3-7", "C3-9", "C3--102", "C3-88"
        }
        ||
        (
            'Project-Resource'[Id] = "C3102" &&
            LEFT('Project-Activity'[Id], 14) = "C3-S1b.2.1"
        )
        ||
        (
            'Project-Resource'[Id] = "C3-M-88" &&
            LEFT('Project-Activity'[Id], 13) = "C3-S3.3.7"
        )
    )
)

Both approaches will provide the desired result by summing PlannedUnits with the required filtering conditions. The first approach uses OR() to handle the "OR" logic explicitly, while the second approach consolidates the conditions within a single FILTER block. Depending on your preference and the complexity of your data model, either approach will work effectively.

Best regards,