Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
zenisekd
Super User
Super User

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 4
ThxAlot
Super User
Super User

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.



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LearnAndPractise(Everyday)


)



mark_endicott
Super User
Super User

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

Nope.

zenisekd_0-1738151426889.png

 

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

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.