Welcome to Excel Jet Consult bog post. In this tutorial, we will learn how about the COUNTROWS DAX function. Let’s get started
- COUNTROWS Function counts the number of rows in a table
- COUNTROWS Syntax =COUNTROWS(table)
In the caption below, we have our fact table named Transaction_tbl; dimension table named Dim_tbl and calendar dimension table named DimDate. In the second caption below, we have established one to many relationships between the dimension tables and the fact table
The next thing we will do is write an explicit DAX Measure:
- In the Data view, In the Calculations group of the Modelling tab, click on New Measure
- In the formula bar, execute the DAX formula: Transaction = COUNTROWS(Transaction_tbl)
- Click Enter
- In the Report view, click on Table visualization
- Drag and drop Account Manager from the Dim_tbl and Transaction measure into the Values area
From the caption above, we can see that there are Total of 61 transaction generated by all the Account Manager. Abiola David has 4 Transaction; Connor Rulibeth has 11 and so on.
When we drag and drop Date field from the DimDate into the Values area below Transaction, we have more details such as Year, Quarter, Month and Day (Date Hierarchy) that each Transaction was generated. For instance, all the four transaction generated by Abiola David occured in the Months of March, June, July and September respectively. See the caption below
