Calculation with multiple OR conditions

[zenisekd]

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"

        OR

      '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

[ThxAlot]

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.

[mark_endicott]

@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.

[zenisekd]

Nope.

[mark_endicott]

@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.