DAX COUNT and COUNTX – Impact of Using Measures and Columns
In a previous post we looked at DAX SUM and SUMX expressions. We introduced a lot in that article. We mentioned table functions, iterators, row and filter context. We also looked at some measures and calculated columns. Did you notice in that article and video how there can be a different impact using measures and calculated columns?
In this article we are going to slow things down a little. We will look at DAX COUNT and COUNTX – Impact of Using Measures and Columns. Measures and columns work very different. Today, using Count and COUNTX you will see an example of how measures and columns can impact the results in a table or visualization.
COUNT comes from Excel and will count the number of cells in a column that contain a number. COUNT will include all fields that contain a zero. But it will exclude a field if it is blank. Text & true/false are excluded. If you want to count text or logical functions, you need to use COUNTA.
You will not (yet) find COUNTX in Excel. It’s a new function to DAX. It works very like SUMX. COUNTX takes two arguments. the first must return a table and the values to count are the second argument. Like COUNT, COUNTX counts numbers so if you want to count text or logical functions you need to use COUNTAX.
Let’s hop into an example and have further look at COUNT and COUNTX. And the impact of creating a calculated column v’s a measure.
We are going to use the same data that we used in the previous article on SUM and SUMX.
First, we have a sales table. This table contains the following columns:
date, invoice number, customer ID, product ID, quantity and sales.
We also have a Product table. The columns in this table are: product ID, category, name, color, supplier ID, cost price and sales price.
COUNT by Calculated Column
We will start by adding a calculated column to our products table. In this calculated column we will enter the expression:
=COUNT (products[Cost Price])
Before we hit enter, think for a moment about what this formula will return? Will it return 1 because it’s looking at the cost price for that row and that row only; so can only count 1? Or will it return 12 because there are 12 cost prices in the table? Is it going to return something else?
The function returns 12. A calculated column defines the rows. But the COUNT function tells the DAX engine to count all the fields in the column with a number. You see COUNT is an aggregation functions, which implies the rows to be all the rows in the cost price column.
COUNT by Measure
Let’s now create a measure using the same function
=COUNT (products[Cost Price])
Again, we get 12 because using the COUNT aggregation function defines the rows.
COUNT Measures and Columns in Pivot Tables
To look at this further let’s put this into a pivot table.
On the row labels we will drop in the products name. Then into the values field we will drop in our calculated column ‘count of cost price’.
The first thing you will note is that the counts are not correct. This is because in a calculated column the values are aggregated by SUM. If we change this from SUM to COUNT then we get the correct value.
But who want to go through this extra step? There is no real need for the calculated column. You could drop the sales price into the value and change the aggregation from SUM to COUNT!
What We Learned So Far
Calculated columns carry out calculations in the table within the column. Row by row. Aggregation then happens in the pivot table, based on the filters. The filter in this case is ‘products name’.
Whereas when you create a measure the values are not calculated in the table. The measure and the filter placed on the pivot table define the rows to carry out the calculation on.
It is much easier to slice and dice a value created by a measure than a values created in a calculated column.
If you are coming from an Excel background, the logical thing to do would be produce an extra column in your data. But when you are using DAX, things are a little different.
COUNTX By Calculated Column
Now let’s look at COUNTX. Remember we said COUNTX is an iterator. That means it will work its way through the table and evaluates an expression for each row. it will then store that value and aggregation of these values will happen at the end.
What we will try to do next is count the number of a set of values in the products table. But we will filter the table for the product category Shoes.
First, we will create a calculated column and we will enter the following DAX function:
CCcountshoes =COUNTX (FILTER(products,products[Product Name]=”Shoes”),products[Cost Price])
What we can see is that for each row, the calculated value is 2. You see we define the row context by using a calculated column. In the first row DAX is saying, okay let’s filter the product name to give me shoes only. Then count the rows that contain product cost prices. The answer is 2.
In the next row and later rows DAX does exactly the same thing. It’s says to its self, lets filter the products table to only give us shoes and then count the row that contain a cost price. That is why for each row, you get the same value.
COUNTX By Measure
Let’s now create a measure and we will use the same syntax that we use for the calculated column
=COUNTX (FILTER(products,products[Product Name]=”Shoes”),products[Cost Price]) and we will name this measure Count Shoes.
The result we get is 2 and DAX carried out the calculation only once on the table. You see COUNTX is an iterator. So DAX say to itself, lets filter the product name to shoes. Then, using the table returned by the filter, focus on the Cost price column. COUNTX irritates over each row of the table and counts the values in the cost price column.
COUNTX Columns and Measures in Pivot Tables
Let’s take a look at the difference returned in a pivot table when we use a calculated column compared to a measure using an iterator.
We will use our products name in the rows and drop in the calculated column we created to the value. What we would expect to see, as our expression filters the table to only shoes, is a count of the shoes cost price. But instead first we get a sum aggregation, like we did with the count calculated column.
Let’s try changing the aggregation to COUNT. We see we get 1 in Boots, but we don’t have any Boots that are Shoes. So these values don’t mean too much.
Let’s drop in our measure. Now we can see that we have a value for Shoes and no value under any of the other product names.
Explaining what happens
In this pivot table, with the measure, DAX says to itself, let’s go to the products table and the first item is Boots. So, our table is first filtered by Boots. Then when it has this table it says to itself, okay in this table of Boot, find all the Shoes and count the cost price. But there are no Shoes that are Boots and so there is no value to return. Do you get it!?
The calculated column works different. The expression is first calculated in the table, row by row, returning a count of 2 on each row. In the pivot table these values are then aggregated. But in the column, there is 1 product name Boots, and that does contain a value. So the pivot tables includes that value.
DAX COUNT and COUNTX – Impact of Using Measures and Columns
What you need to understand now is that calculated columns and measure work different. This must be taken this into consideration when preparing your DAX calculations.
It is also important to understand the difference between aggregation functions and iterating functions. Aggregation functions such as SUM, COUNT and AVERAGE will take all the rows in the selection as implicated rows. Iteration functions will explicitly state the rows to be used.
We mentioned earlier that if you need to count text or logical functions you need to use COUNTA and COUNTAX and these count function are part of the DAX statistical functions. There are also a number of other count functions that can be used in the mix and we will see more of them as we progress but for the moment just so you know.
DISTINCTCOUNT will count the distinct values in the selected data.
COUNTROWS will count the rows of data in a table.
COUNTBLANKS will count all the blank rows in a table.
Sign up for my newsletter – Don’t worry, I wont spam. Just useful Excel and Power BI tips and tricks to your inbox
In return for this Tip – DAX COUNT and COUNTX – Impact of Using Measures and Columns – I ask that you share this post with your friends and colleagues
Learn and Earn Activity
To earn steem rewards on this post, in the comments section below answer the following questions:
Before you read this article and watch the video, how would you rate your understanding of COUNT and COUNTX functions in DAX? Poor, Ok or Great?
How would you rate your understanding now that you have completed the article?
Calculated columns and measures often give different results. Can you explain in the comments below why this might happen?
What are your key takeaways from this post?
Now there is value in Learning with The Excel Club and our Learn and Earn STEEM activities.
We are the first Excel, PowerBI and DAX blog in the world where you can Earn while you Learn.