cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dscott73 Regular Visitor
Regular Visitor

Create measure based on value in another fact table where value > 0

I have two fact tables.  One is for Production Runs.  The other is for Scrapped Items.  The only relations ship between these two tables in the Product Key.  There are date fields, but you may have a Production Run on Monday, and not Scrap any thing until Tuesday.  But there is no common "ProductionID" column I can use.

 

In the Production Run Fact I have Product Key, Qty Good, and Qty Bad.

 

In the Scrap Fact table there is Product Key, Qty Scrapped.

 

I have a Product Dim table and a Date Table.

 

I can drag a Table visualization onto the design surface.  Drag the ProductID from the Product Dim table to it.  Then drag Qty Good and Qty Error from the Production Run table, and then drag over the Qty Scrapped from the Scrap Fact Table.

 

I end up with something like this:

 

ItemID     QtyGood     QtyError     QtyScrap

---------------------------------------------------

Item1      10          1            3

Item2                               2

Item3      5           0            1

 

I can create a "Total Produced" calculated column which is QtyGood + QtyError and then filter the visualization by this column, and set it only display rows where "Total Produced > 0".  This is fine for the visualization, however any Card visualizations you cannot set this same filter. Thus using the above example. The Table (with visualization filter applied) whould show the Column Total for QtyScrap as 4, but the card would display 6.

 

What I have been trying to is come up with a formula for a measure where I only include QtyScrap when Total Produced > 0. This measure would essentially mimic the filter applied to the visualization (Total Produced > 0)

 

I have attempted to use various table filter formulas with no succes.

 

Would it just be easier to do this in the SQL?  Though, I am curious if there is a way to do this in DAX.

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ross73312 Super Contributor
Super Contributor

Re: Create measure based on value in another fact table where value > 0

I'm expecting you want  a measure along these lines:

 

QtyScrapped = var Total_Produced = SUM('ProductionRun'[QtyGood]) + SUM('ProductionRun'[QtyError])
RETURN
IF(
	Total_Produced > 0,
	SUM('Scrap'[QTYScrap]),
	0
)

   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


View solution in original post

1 REPLY 1
Ross73312 Super Contributor
Super Contributor

Re: Create measure based on value in another fact table where value > 0

I'm expecting you want  a measure along these lines:

 

QtyScrapped = var Total_Produced = SUM('ProductionRun'[QtyGood]) + SUM('ProductionRun'[QtyError])
RETURN
IF(
	Total_Produced > 0,
	SUM('Scrap'[QTYScrap]),
	0
)

   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors