Forum Discussion
Multiple Fact Tables
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))
Community Champion
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).
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
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))Sounds about right. Thank you for the responses!