Forum Discussion
Average Order Lines Count
Hi Harris,
I've managed to solve it by bringing Order Number and Order Line to the fact table and then creating a measure like so:
=AVERAGEX(SUMMARIZE('fact';'fact'[OrderNo];"AVG";DISTINCTCOUNT('fact'[OrderLineNo]));[AVG])
Not sure this is the best approach but it works.
Thanks for your help,
Storing order number and order line in the fact table is a legitimate star schema design (called a degenerate dimension):
A dimension that has the same number of rows as a fact table is a heads up that a degenerate dimension might be useful.
(Sorry if you already know this)
Thanks for your input leonardmurphy
I am familiar with the concept of degenerate dimensions but in this particular case i had two concerns. First is that my transaction dimension actually includes several other relevant fields and hierarchies and so i can't just get rid of the dimension.
The other part is related to keeping the pover pivot model as "lean" as possible. Since data is stored by column duplicating such high cardinality columns is expensive.
For the moment i am testing on only a few million rows and haven't really noticed any performance issues but later on the full data set i might need to revisit the approach or try optimizing the model. Plenty of awsome resources about it here if interested.
Regards