topic Calculation with multiple OR conditions in DAX Commands and Tips

Calculation with multiple OR conditions

zenisekd — Wed, 29 Jan 2025 11:13:32 GMT

Hi, I was wondering, what is the most elegant solution, to write a measure, that contains multiple OR conditions over several related tables.

I have the following to work with:
measure SALES (calculates sales from the 'INV1 - AR invoice' table)
Columns that will be used in conditions:

'INV1 - AR invoice'[Project]
'OCRD - Customer'[Market], related to 'INV1 - AR invoice'
'OCRD - Customer'[Industry ID] related to 'INV1 - AR invoice'
'OSLP - Salesperson'[OUSR.Country] related to 'INV1 - AR invoice'

Now I need to calculate Sales, which uses ALL salesperson, but only for

'OSLP - Salesperson'[OUSR.Country] = "PL"

and includes either of these possibilities:
(
'OCRD - Customer'[Market] = "Clean Energy"

'OCRD - Customer'[Industry ID] IN { "H2", "HINFR", "HPROD", "HRFS", "HVEH" }
OR

'INV1 - AR invoice'[Project] IN { "X", "Y", "Z" }
)

I have following code, but I am not sure how to best put to OR conditions:

Sale II. PL2 =

CALCULATE(

[Sales],

FILTER(

ALL('OSLP - Salesperson'),

'OSLP - Salesperson'[OUSR.Country] = "PL"))

Thanks

Re: Calculation with multiple OR conditions

mark_endicott — Wed, 29 Jan 2025 11:27:35 GMT

zenisekd - try this:

Sale II. PL2 = CALCULATE( [Sales], ALL('OSLP - Salesperson'), 'OSLP - Salesperson'[OUSR.Country] = "PL" , ( 'OCRD - Customer'[Market] = "Clean Energy" || 'OCRD - Customer'[Industry ID] IN { "H2", "HINFR", "HPROD", "HRFS", "HVEH" } || 'INV1 - AR invoice'[Project] IN { "X", "Y", "Z" } ) )

If this helps, please mark as the solution to help others with the same challenge.

Re: Calculation with multiple OR conditions

zenisekd — Wed, 29 Jan 2025 11:50:33 GMT

Nope.

Re: Calculation with multiple OR conditions

mark_endicott — Wed, 29 Jan 2025 12:23:49 GMT

zenisekd - My bad, always forget about that rule. We're going to need to create a cartesian product of all possible values, so hopefully this will work, or atleast set you on your way:

CALCULATE ( [Sales], FILTER ( CROSSJOIN ( ALL ( 'OSLP - Salesperson'[OUSR.Country] ), ALL ( 'OCRD - Customer'[Market] ), ALL ( 'OCRD - Customer'[Industry ID] ), ALL ( 'INV1 - AR invoice'[Project] ) ), 'OSLP - Salesperson'[OUSR.Country] = "PL" && ( 'OCRD - Customer'[Market] = "Clean Energy" || 'OCRD - Customer'[Industry ID] IN { "H2", "HINFR", "HPROD", "HRFS", "HVEH" } || 'INV1 - AR invoice'[Project] IN { "X", "Y", "Z" } ) ) )

If this helps point you towards the answer, please accept as the solution for others with the same challenge.

Re: Calculation with multiple OR conditions

ThxAlot — Wed, 29 Jan 2025 13:09:36 GMT

Follow this pattern to solve OR logic among different slicers,

= CALCULATE( SUM( tb[sales] ), FILTER( CROSSJOIN( ALL( slc1[col] ), ALL( slc2[col] ), ALL( slc3[col], slc3[_col] ) ), slc1[col] IN { } || slc2[col] IN { } || slc3[col] IN { } || slc3[_col] IN { } ) )

I assume you've got enough DAX knowledge to fit the pattern in your real-life scenario.