cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dkernen
Helper III
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
dedelman_clng
Super User II
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!




View solution in original post

1 REPLY 1
dedelman_clng
Super User II
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!




View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

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.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.