Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

DAX Cartesian Product - Assistance Required for correct Formula Expand / Collapse
Posted Tuesday, August 19, 2014 10:19 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 5, 2016 7:09 AM
Points: 54, Visits: 505

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.


  Post Attachments 
Test BI SQL.xlsx (3 views, 414.80 KB)
Test BI SQL.txt (4 views, 2.01 KB)
Post #1605013
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse