cancel
Showing results for
Search instead for
Did you mean:  Helper III

## Calculate a metric with OR logic from two different tables

Hello.  I believe I have a relatively simple ask but one that I cannot figure out.

I have included a link to a sample dataset.  https://mwtn-my.sharepoint.com/:u:/g/personal/dkernen_mwtn_org/EZt6qdxeDXhJpZjw3Jaay4wBe5-VaTaxIwgjy...

I have three tables: dimension for SHAPES, dimension for COLORS, and a fact for SALES.  I know how to make measures for when color="Red" or when shape = "Round"  or when color=Red AND shape="Round" but I need to create a measure for when color="Red" OR shape="Round"

Any help would be most appreciated!  Thank you so much!

@CALCULATE, @ORlogic

1 ACCEPTED SOLUTION  Super User II

Hi @dkernen - have you tried adding the separate results of "where color = red" and "where shape = round" ?  This would double-count those that are red and round, so you could then subtract "where color = red AND where shape = round".

``````RedOrRound =
var __Red = CALCULATE([Total Sales], Colors[Color] = "Red")
var __Round = CALCULATE([Total Sales], Shapes[Shape] = "Round")
var __RandR = CALCULATE([Total Sales], Colors[Color] = "Red", Shapes[Shape] = "Round")
return
(__Red + __Round) - __RandR
``````

Hope this helps

David

Proud to be a Super User!

1 REPLY 1  Super User II

Hi @dkernen - have you tried adding the separate results of "where color = red" and "where shape = round" ?  This would double-count those that are red and round, so you could then subtract "where color = red AND where shape = round".

``````RedOrRound =
var __Red = CALCULATE([Total Sales], Colors[Color] = "Red")
var __Round = CALCULATE([Total Sales], Shapes[Shape] = "Round")
var __RandR = CALCULATE([Total Sales], Colors[Color] = "Red", Shapes[Shape] = "Round")
return
(__Red + __Round) - __RandR
``````

Hope this helps

David

Proud to be a Super User!  ## Helpful resources

Announcements #### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group #### The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings. #### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event. Top Solution Authors
Top Kudoed Authors
Users online (17,409)