SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DAX Cartesian Product - Assistance Required for correct Formula


DAX Cartesian Product - Assistance Required for correct Formula

Author
Message
darrenkelly
darrenkelly
Old Hand
Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)Old Hand (354 reputation)

Group: General Forum Members
Points: 354 Visits: 575
Hi

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.

Thanks
Darren



Attachments
Test BI SQL.xlsx (10 views, 414.00 KB)
Test BI SQL.txt (13 views, 2.00 KB)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search