How To Use The COUNTROWS DAX Function In Virtual Tables

No comments

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.

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.

countrows dax

In this case, I’m going to use the Sales table, since I already have that physical table.

countrows dax

Using the Sales table also makes sense in this case because I’m just looking for the Total Orders per day.

countrows dax

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.

countrows dax

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.

You can see the difference between the results of our physical Sales table and our virtual table through the visualization below.

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.

countrows dax

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.

countrows dax

Once again, I used COUNTROWS as my main function here. But this time, I’m going to use CALCULATETABLE to create a virtual table.

countrows dax

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.

countrows dax

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.

Here’s another thing that’s interesting with the results I’m seeing.

In this particular date I’ve highlighted, you’ll see that a total of 3 customers who were part of the top 50 made purchases on the same day. However, only one of them showed up on the list of those who purchased products worth more than 15,000.

Once I double check the data under our top 50 list, I also see that there are indeed two other people who also made purchases on that day, bringing the total to 3.

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

***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI
FREE COURSE – Ultimate Beginners Guide To DAX
FREE – 60 Page DAX Reference Guide Download
FREE – Power BI Resources
Enterprise DNA Membership
Enterprise DNA Online
Enterprise DNA Events

This image has an empty alt attribute; its file name is membership-banner-3-e1539659838128.png

***** 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

***** Related Course Modules *****
Mastering DAX Calculations
Business Analytics Series
DAX Formula Deep Dives

***** Related Support Forum Posts *****
Using COUNTROWS But Also Managing Many Filters – DAX Formula Optimization
Loyalty Tracking
If Is Blank In Countrows Card Visual
For more COUNTROWS support queries to review see here…

Enterprise DNA Events


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.