SQL Server 2012 – COUNT Function in DAX for BI Semantic Model

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 TwitterFollow me on FaceBook

   

Leave a Reply

Your email address will not be published.