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 (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

4 Replies

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

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

  • ThxAlot's avatar
    ThxAlot
    Icon for Super User rankSuper User
    1 year ago

    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.