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

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