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

[dscott73]

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.

[Anonymous · Accepted solution]

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
)