Multiple Fact Tables

[zskolnik]

2 parter here

I have multiple fact tables and I'm looking to build a report that can pull pieces from each of them. The model looks as follows:

I don't think there are any chasm/fan traps in there, as each order has an associated order, which has an associated account.

When I pull Total Revenue from Orders and count of Account name by countries/market segments I get the model working as it should. However, if I also put in order type from Orders, the model doesn't seem to like that.

Looking good (sc_name from countries table, name from accounts table)):

Looking not so good:

You see repeating values in the count of account name, and blanks showing up for order status where there's no rev.

I feel like I'm doing something in my model wrong?

Further to this, I want the model to be able to pull from Order Details, and eventually look like the below, which I know will compound the problem.

Thanks for your help!

Z

[amitchandak]

@zskolnik , At a high level the model seems fine, but need to check for calculation and how it there in power bi

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[AllisonKennedy]

@zskolnik 

Can you share redacted screenshot of actual data or maybe sample report with the dummy data you've provided?

Couple of questions:

1) I feel like the Count of Name in the Not looking so good sample should be 3 repeated, not 1??? 

2) What visual type are you trying to use? Table or Matrix or other??

The reason you have this problem is because you're trying to put Accounts in the Value field, when the cross filter direction doesn't allow for it. With your current model setup, you have to look at the bottom most Fact table that you are using in the current visual, and that is the ONLY fact table that can have aggregations on it or go in VALUES of any visual. 

Two options: 

1) Use COUNT of Account ID from the Orders table instead of Count of Name from the Accounts table.

2) Change the cross filter direction to BOTH on the relationships (careful though as this has other implications so you should read up and understand this before choosing it as best solution).

[zskolnik]

Thanks for the response Allison!

Below is some dummy data that should help. 1) You're correct in that in my sample before the count should have been 3 and not 1. My mistake. 2) Ideally a matrix

Your explanation below, that only the bottom-most fact table can be an input to the values column helps, and that would explain why it wasn't working. Your idea to use count from the orders table does work (as seen below - account id is from accounts, customer id from order), thank you for that! However, I would then run into difficulties when I go one level down to the salesorderdetails table, as I'd like to figure out what specific products make up that order (from the products table).

As would be expected, if I drop in the names of our products from the product table into rows, I get all our products below which clearly breaks the model. I wouldn't like to use bidirectional filters as I am now for reasons that you mentioned. 

Any ideas how I could find what products make up the orders from France with an accurate customer count?

Thanks,

Z

Matrix working down to orders

Matrix breaking on products

Model

[AllisonKennedy · Accepted solution]

@zskolnik  Yup, you'll need to get into some fun DAX expressions. 

Basically, you're doing to need to use DAX to add a series of MEASURES to your data which in effect pull the information from that bottom most FACT table. 

Start with this one: 

TotalOrders = DISTINCTCOUNT(SalesOrderDetails[Order Number])

Then use that TotalOrders Measure as a filter for any other calculations, such as:

Total Accounts =

COUNTROWS(FILTER(Account,[TotalOrders]>0))

[zskolnik]

Sounds about right. Thank you for the responses!