Thursday, November 7, 2013

Distinct Count in SSAS

In some scenarios we may need to get the Distinct Count value. As an example we may need to get the items purchased from a particular supplier. In such scenario we need to get the distinct items from the purchase orders, since the same item will be available in different purchase orders.
For that below expression can be used.

   1: DistinctCount(Nonempty([Item].[Item].[Item].Members, [Measures].[Fact Purchase Count]))

FactPurchase is our Fact table which stores Purchase Order data and Item is the dimension which keeps Item information. By default when we create a Measure Group in the Cube, it automatically adds the Count measure. In the above expression what it does is, it joins the Item Dimension with that Count measure and get the distinct Items from it.

No comments:

Post a Comment