COUNT Function in BISM
Hello!
We at SQLServerGeeks.com are committed to spread the knowledge as we gain in the process of evaluating the newly released SQL Server 2012 CTP 3 release code named “Denali”.
This post comes as a supplementary addition to the earlier post which describes the RELATED FUNCTION.
In this post I plan to showcase examples for COUNT and related Count function in DAX. We have 5 different COUNT related functions available for data manipulations. Let us have a small walk through for each of them.
Please note that I am using the AdventureWorksDWDenali as my database.
COUNT
Name of function: COUNT(<<column_name>>)
Description: Count number of rows in a column having a value.
Example: COUNT_GEOGRAPHY_CITY:= COUNT(Geography[City])
Result: Measure column ‘Geography'[COUNT_GEOGRAPHY_CITY]: The function COUNT takes an argument that evaluates to numbers or dates and cannot work with values of type String.
So this is our first learning. COUNT function works with only numbers.
So a valid function could take form like:
COUNT_GEOGRAPHY_ID:= COUNT(Geography[Id])
But, what if I need a count of rows where the value does not evaluates to number?
COUNTA DAX function comes to rescue.
COUNTA
Name of function: COUNTA(<<column_name>>)
Description: Count number of rows in a column having a value.
Example: COUNT_GEOGRAPHY_CITY:= COUNT(Geography[City])
This new measure composed works and on my screen it gave me a value of 655
So, in our second learning, if the column is not composed of numbers, COUNTA can be used to determine the count.
COUNTROWS
Name of function: COUNTROWS(<<table>>)
Description: This function can be used to count number of rows in the table.
Example: COUNT_GEOGRAPHY:= COUNT(Geography)
But, I feel the measure created above does not do a complete justification to the function. So, let’s enhance this function slightly, such that it would give me the count of all City where Name = York
COUNT_CITY_YORK:=COUNTROWS(FILTER(Geography,Geography[City]=”York”))
This new measure composed works and on my screen it gave me a value of 3
What just happened is that the scope of the table was altered and the COUNTROWS now works on the new scope.
How about this calculation:
COUNT_CITY_YORK:=COUNTA(FILTER(Geography,Geography[City]=”York”))
I will introduce you to FILTER soon
This will not work. Reason: COUNTA expects a column as an argument.
COUNTBLANK
Name of function: COUNTBLANK(<<column_name>>)
Description: This function can be used to count blank rows in a column in any given table.
Example: COUNT_BLANKS:= COUNTBLANK(Product[Product Subcategory Id])
This new measure composed works and on my screen it gave me a value of 209
COUNTAX
As in BOL,
The COUNTAX function counts nonblank results when evaluating the result of an expression over a table. That is, it works just like the COUNTA function, but is used to iterate through the rows in a table and count rows where the specified expressions results in a nonblank result.
Name of function: COUNTAX(<<table_name, expression>>)
Example: COUNT_CITY_YORK:= COUNTAX(FILTER(Geography,Geography[City]=”York”), Geography[City])
This new measure composed a works and on my screen it gave me a value of 3
That’s all. Happy Learning!
Regards
Raunak Jhawar
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
