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))
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
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:
Sounds about right. Thank you for the responses!