June 11, 2015 at 3:34 pm
Hello Everyone,
I have a fact table that has some basic totals: Order Quantity, Order Dollars, Return Quantity, Return Dollars, etc...
I have about 5 dimensions for that table as well. What I am looking to do is pull a distinct count on Orders where the order quantity is > 0. I have looked online everywhere and can't seem to a solution.
My fact table has a calendar dimension, an order type dimension, and item dimension. However - I just need that distinct order count where the order quantity is > 0. Has anyone had any success in creating such a measure or calculated measure?
June 12, 2015 at 1:11 am
smrobin (6/11/2015)
Hello Everyone,I have a fact table that has some basic totals: Order Quantity, Order Dollars, Return Quantity, Return Dollars, etc...
I have about 5 dimensions for that table as well. What I am looking to do is pull a distinct count on Orders where the order quantity is > 0. I have looked online everywhere and can't seem to a solution.
My fact table has a calendar dimension, an order type dimension, and item dimension. However - I just need that distinct order count where the order quantity is > 0. Has anyone had any success in creating such a measure or calculated measure?
Distinct counts (both measure groups and calculations) are notorious for less than perfect performance, although I'm not sure what you are trying to distinctly count here so there's a couple of ways to do it. The first (and best performing by miles) would be to put a simple Named Calculation in your DSV (or if you are using views in the db, put it there) in the fact table that said something like
CASE WHEN OrderQuantity > 0 THEN 1 ELSE 0 END
and then just add that as a new summed measure in your measure group.
In MDX it depends what your are wanting to count. Let's say you wanted to count the number of distinct items in all of your orders then it would be something like:
DISTINCTCOUNT(
FILTER([Product].[Item].[Items].Members, [Measures].[Order Quantity]>0)
)
Obviously I don't know your dimension names etc. but something along those lines. I'd go for the first option if you can though. 🙂
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply