Counting active products/clients/specialist over related dimensions

23-11-2018  0 Comment(s)

There is a simple question that could be asked in any industries for business understanding: how many active products so far? or how many new/active clients we have in this year/month?. Active products/clients is a term to describe the clients/products that is appeared in recently transactions. I now re-produce the problem when trying to answer this question.

What i have so far:

  • A Fact table (FactInternetSales) from AdventureWorksDW database
  • A dimension (DimProduct)

I will use DISTINCTCOUNT to count on ProductKey in FactInternetSales table by creating calculated measure:


No. of using products = DISTINCTCOUNT(FactInternetSales[ProductKey])

And I also drag&drop the ProductKey field of DimProduct to Card and choose distinct count for checking purpose:

So there are 158 active products (or 158 of 606 products are using so far).

What if my manager wants to see how many active product sub category do we have up to now? It’s problem now cause I’m using snowflake schema so I do not have ProductSubCategoryKey in Fact table (FactInternetSales). First idea is creating a measure with ProductSubCategoryKey in DimProduct as below:


No. of using subproduct (counting on DimProduct table) = DISTINCTCOUNT(DimProduct[ProductSubcategoryKey])

I got a value but it’s incorrect cause it counts all of productsubcategorykey in DimProduct table.

My solution for this query is using filter context to explicitly indicate the filtering on Fact table. I do a little bit changes with above measure’s expression:


No. of using subproduct (counting on DimProduct table) = CALCULATE( DISTINCTCOUNT(DimProduct[ProductSubcategoryKey]), FactInternetSales)

Comment Here


No Comments to Show