I want to dive deeply into how useful the COUNTROWS DAX function can be. You could be a newbie who hasn’t fully grasped how COUNTROWS works. You could also be a more experienced Power BI user but are unsure of how you can maximize the function. You may watch the full video of this tutorial at the bottom of this blog.
Through the examples I’ll be giving below, you’ll see how powerful COUNTROWS is as a DAX function, especially when used in virtual tables.
Using The COUNTROWS DAX Function In A Physical Table
COUNTROWS allows you to count the number of rows in any table that you’re referencing.
So the moment you use it in a measure, it will automatically ask you for a table as well.
In this case, I’m going to use the Sales table, since I already have that physical table.
Using the Sales table also makes sense in this case because I’m just looking for the Total Orders per day.
Since every single order covers one row in my data set, the COUNTROWS function will only go through every single row with an entry to give me the results I need.
Of course, that’s the simplest way to use COUNTROWS. What I want to show you is that this function can also be used for more advanced applications, especially in virtual tables.
A Simple Example Of How To Use COUNTROWS In A Virtual Table
I’m going to give two examples of how to use COUNTROWS in a virtual table. The first example is really simple to use.
This time, instead of just looking at the Total Orders, I want to check how many orders were there on any particular date that exceeded 15,000.
I’m going to use COUNTROWS again. But this time, instead of putting the Sales table directly within the COUNTROWS function, I’m going to put it inside FILTER instead.
Again, I’m looking at every single row here. But this time, I’m looking for rows with a revenue of 15,000 and up. So as the measure goes row by row, it only retains the count for all the rows that meet those parameters.
So COUNTROWS is doing the same thing here as it did when I used the physical table earlier. The only difference is it’s now being applied to a virtual table where I can put in my own parameters.
From here, it’s easy enough to add more parameters to the measure. For example, I could also check what percentage of the daily sales exceeds the 15,000 mark.
An Advanced Example Of Using COUNTROWS In A Virtual Table
This time, I’ll apply the COUNTROWS function in a more advanced manner. I’m going to look at how many orders on any particular day came from my top 50 customers of all time.
I’ll start off by looking at my data set and checking out how many customers I have all in all. Looking at the numbers, I see that there are 3,603 customers in total.
I have set up the data here in a way that only the customers who made an actual purchase are on the list.
You’ll also notice that I have a date slicer on my report here. But because of the parameters that I’m looking at, this wouldn’t matter. I’m looking at all customers since the beginning of time.
This is how I want my measure to work: I want it to look through every single sale for each day and assess whether any of those sales came from a top 50 customer. So my measure is going to look like this.
Once again, I used COUNTROWS as my main function here. But this time, I’m going to use CALCULATETABLE to create a virtual table.
I’m still going to use my Sales table here, but I’m going to add more logic to it by only looking at the top 50 customers. This is why I’m also going to use the TOPN function.
Since I’m looking at my top 50 customers based on their purchases from the beginning of time, I’m going to include ALL the Dates. Using the ALL function disregards all other filters that may have been placed on the date slicer.
Now that the measure is in place, I’m seeing one customer here who met the requirements on this particular date.
How do I check that this is right?
I can see if I’m getting the right results by clicking on that specific date where there was a sale from one of my top 50 customers.
Then, I’ll look at my visualization here on the right to see if it comes up with a name within my list of top 50 customers.
True enough, I see the name of Charles Henderson. Now, I’m going to cross-reference that to the detailed list down here.
It shows that Charles Henderson purchased products worth 9,983 on that particular day.
The visualizations also add to the ease by which I’m seeing the data that I’m looking for. Here’s a measure that I’ve also used for visualizations in similar scenarios like this.
Just make sure that you’re aware that if you have a date slicer like this, it could potentially affect the results.
In this case, it’s best to use ALL Dates as well.
***** Related Links *****
New Customer Analysis Using Advanced DAX
Time Intelligence In Power BI: How to Calculate The Number of Transactions Made in the Last N Days
Power BI Advanced Analytics: Secondary Table Logic Techniques
Conclusion
The COUNTROWS DAX function is versatile, as shown in the examples I’ve laid out above. You can go from a simple and straightforward application on a physical table or jump to a more advanced scenario using virtual tables.
It’s also interesting to see how well it works with other table functions like CALCULATETABLE, ALL and TOPN. It can make your measures work for any possible scenario you can think of as long as you find the right combinations for the logic you want to add.
All the best,
Sam
