topic Re: Multiple OR condtions within calculate in DAX Commands and Tips

Multiple OR condtions within calculate

san_bxl — Thu, 09 Jan 2025 08:06:30 GMT

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

Re: Multiple OR condtions within calculate

johnt75 — Thu, 09 Jan 2025 16:46:57 GMT

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

Re: Multiple OR condtions within calculate

Poojara_D12 — Fri, 10 Jan 2025 05:36:55 GMT

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

Re: Multiple OR condtions within calculate

San_Raz — Fri, 10 Jan 2025 05:54:17 GMT

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

Re: Multiple OR condtions within calculate

Poojara_D12 — Fri, 10 Jan 2025 06:04:16 GMT

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

Re: Multiple OR condtions within calculate

Anonymous — Mon, 13 Jan 2025 09:13:04 GMT

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.