Counting number of Products, customers or any dimension

DAX DAX Calculations
1

Hello all,

I think it’s a really basic question, but I am stuck honestly.

Lets consider a simple example in which we have a fact Sales table and 3 dim tables for Products, Customers and Date.

How should you count the number of products:
(1) ProductsDim = DISTINCTCOUNT ( DimProducts[ProductID] )
or
(2) ProductsFacts = DISTINCTCOUNT ( FactSales[ProductID] )

So basically; counting via dimension table or counting via fact table ?

When you do option (1) it won’t correspond so slicers of the year or customer, it always gives the number products in the dimension table, as long as you stay away from bi-directional filtering.

Then you need to create another measure in which you calculate the number of products with a sale, so your measure would look something like this:
(3) CALCULATE ( [ProductsDim] , FactSales )

And then probably use measure (3) everywhere and you probably won’t touch (1) anymore.

My question is: what is the best practice and why ?
(a) : creating two measures to get a “resposnive” number , measure (1) and (3)
(b): creating one measure, directly over fact table, measure (2)

2

I think it depends on what questions you are asking. Are you asking questions of the data from a Sales perspective or are you asking questions from a Product perspective (Total type of products Available in the Product catalogue. As a rule, I create all almost all my measures based on Fact table rather than dimension tables unless I want a count of total products on my Products Catalogue.

3

first thing i won’t do is never use “dim” or “fact” as a variable in a measures. Use something like CountProduct = discountcount(etc)

You don’t want to confuse what a fact and dim table and measures

4

Thanks!

I agree on whay you say and it is the way I am working, but I wasn’t sure if I was doing anything “wrong”, since I also saw @sam.mckay doing it the “indirect” way in the " Ultimate Beginners Guide to DAX" course.
However, personally it seems to be be more direct / efficient to do it over the fact table indeed. Of course, will depending on what you are analyzing, but in most cases probably from a sales perspective.

Thanks for response! It gives me the confidence that what I was originally doing is “correct”.

While you gave a good answer to my question, I will leave the topic open for a few days, maybe still get some insightfull replies or maybe sam can explain why he did it the way he did in the course.

5

Thanks for your response Keith. I can understand that and won’t use it in my reports in this way, but it was more for illustration purposes.

6

@DashboardingMeesters ,

A good general rule is that you want to aggregate over fact tables, and filter over dimension tables.

With regard to your question about counting products, it depends on the question you are asking. Assuming referential integrity (that there is one entry in the dimension table for every entry in the fact table over which the tables are related), you will always have greater than or equal to distinct products in the products table versus distinct products in the fact table (it is fine to have products in the dimension table that have no sales in the fact table, but not vice versa).

Thus, if you want to know the universe of products available for sale, you will use the dimension table, whereas if you want to know the universe of products for which there were one or more sales, you will use the fact table.

Is that helpful in answering your question?

– Brian

1 Like
7

Thanks for your elaborate answer Brian. For sure this is helpfull

I think it makes it all clear and once more a validation that the way I was working was correct.

1 Like