I'm having an issue in writing a DAX formula to deal with Confirmed dimensions and two fact tables. The problem stems from the fact that records don't exist in each fact table and when I place the measures on the report I get a Cartesian product.
I've created a sample POC which is attached. We have a Sales Fact Table and an Employment Fact Table. Both of these tables link to a Customer and a Date. Sales can be further broken down into type (this is where the Cartesian product comes up).
When Sales type is introduced then the Employment Measure is displayed each time, even when there is no actual sale against the type. This makes sense as Employment has no bearing on Sales type other than its related to a Customer (Cartesian product)
Therefore is there a way to remove Sales Type showing up when adding in Employment numbers. For example:-
• Joe has no sales but his employment records shows up for every available Sales Type. In this instance I would like to get Joe, Year, Employment Number
• Mary has both Sales and Employment for 2013. Sales are only for Ireland and as such I only want:-
○ Ireland to show up and Employment against Ireland (accepted and understandable Cartesian product)
Taking Mary set up into account, is the this possible
• Mary, 2013, Ireland, Sales Amt
• Mary, 2013, Employment No
I attached POC Excel sheet and associated simple Database code to create. If anyone can offer advice, its much appreciated.