Forum Discussion

zenisekd's avatar
zenisekd
Icon for Super User rankSuper User
1 year ago

Calculation with multiple OR conditions

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 (ca...
zenisekd's avatar
zenisekd
Icon for Super User rankSuper User
1 year ago

Nope.

 

mark_endicott's avatar
mark_endicott
Icon for Super User rankSuper User
1 year ago

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.