Definition:
- COUNTX is a widely used function in data analysis that calculates the number of rows or entries within a specified table or reference that meet specific conditions defined by an expression. It provides a count of rows where the given condition evaluates to true.
Applicable to:
Calculated Columns ExpressionsReturn Value:
- The return value of the COUNTX function is an integer representing the count of rows within the specified reference where the condition evaluates to true.
Remark:
- COUNTX is particularly useful when you need to count rows based on complex conditions or expressions.
- COUNTX enables you to perform conditional counting and filtering within datasets.
- COUNTX helps to identify and quantify specific patterns or subsets of data.
- COUNTX function can be used across Date, Strings and Numbers.
COUNTX(<expression>,<condition>)
COUNTX in Board Expression
Note
Expressions help you add visual effects to your dashboard through widget-specific conditions. It is important to note that the Expressions are not meant to manage or modify data.
Parameters
Expression | A placeholder in a function that is replaced with the actual widget and measure names. |
Condition | A placeholder in a function that is replaced with the condition filter. |
Widget Name | The specific name or identifier of the widget being used for data visualization in the specific Infoboard. |
Measure | Represents the name of the measure that is being displayed or analyzed using the widget. |
Dimension | Represents the dimension column that is being displayed or analyzed using the widget. |
Dimension Value | Represents the specific dimension items within the dimension column used for the visualization in the widget. |
- Write the COUNTX function. For instance:
COUNTX(<expression>,<condition>)
- Where
expression
equals'Widget Name'[Measure]
andcondition
equals[Dimension Name]="Dimension Value"
- Replace Widget Name with the actual name of your widget, Measure with the required measure name, Dimension with the required dimension name and Dimension Value with the dimension value of the selected dimension name.
- Set the COUNTX function based on the scenario.
- To learn how to configure an Expression in Infoveave, visit the section Configure Expression.
Example
Scenario:
- you're managing a dataset and an associated Infoboard within your Food Sales Analysis System. Your objective is to identify and count the number of customers who qualify for a special "Frequent Buyer" discount.
- Dashboard Name: Food Sales Dashboard
- Widget Name: Customer Loyalty
- Measure Name: Customers (quantifying the number of eligible customers)
- Dimension Name: Purchase Behavior (categorizing customers based on their purchase behavior)
- Dimension Value: "Frequent Buyers" (representing customers who pay their bills on time)
- You can use the COUNTX function like this:
COUNTX('Customer Loyalty '[Customer], [Discount Types] = "Frequent Buyers")>50
COUNTX in Calculated Columns
Parameters
Expression | A placeholder in a function that is replaced with the actual widget and measure names. |
Condition | A placeholder in a function that is replaced with the condition filter. |
Column Name | The name of the column in the dataset or Datasource that contains the values you want to analyze. |
Filter Condition | A filter condition returns the data points that meet the defined condition that you want to analyze. The filter condition can be either a string or a numeric. |
- Write the COUNTX function. For instance:
COUNTX(<expression>,<condition>)
- Replace
<expression>
with required[Column Name]
and<condition>
equals[Column Name]="Filter Condition"
- Replace Column Name with the actual name of your column and Filter Condition with the required condition to be applied on the columns.
- To learn how to add calculated columns in Infoveave, visit the section Calculated Columns.
Alert
- Do not use double quotes when setting up a numeric as the filter condition.
Example
Objective: Consider that you have the below sales dataset, your goal is to count the number of rows in the dataset where COUNTRY is BRAZIL
ORDER DATE | COUNTRY | CATEGORY | UNIT PRICE | MARKET PRICE | QUANTITY |
2024-01-03 | Brazil | Baby Food | 38.41 | 43.7874 | 2 |
2024-01-07 | Japan | Spices | 45.56 | 50.8155 | 20 |
Japan | 43.7874 | 10 | |||
2024-01-18 | 32.3019 | 4 | |||
2024-01-22 | Brazil | Cosmetics | 28.335 | 23.0109 | 7 |
2024-01-26 | Canada | 20.185 | 23.0109 | 9 | |
2024-01-04 | France | Cereal | 25.26 | 28.7964 | 9 |
2024-01-09 | Brazil | Cereal | 44.575 | 50.8155 | 8 |
2024-01-14 | Brazil | Snacks | 20.185 | 4 | |
Brazil | Cosmetics | 40.485 | 46.1529 | 2 |
- You can use the COUNTX function like:
COUNTX([COUNTRY],[COUNTRY]="Brazil")
ORDER DATE | COUNTRY | CATEGORY | UNIT PRICE | MARKET PRICE | QUANTITY | COUNTRY COUNT |
03-01-2024 | Brazil | Baby Food | 38.41 | 43.7874 | 2 | 5 |
07-01-2024 | Japan | Spices | 45.56 | 50.8155 | 20 | 5 |
Japan | 43.7874 | 10 | 5 | |||
18-01-2024 | 32.3019 | 4 | 5 | |||
22-01-2024 | Brazil | Cosmetics | 28.335 | 23.0109 | 7 | 5 |
26-01-2024 | Canada | 20.185 | 23.0109 | 9 | 5 | |
04-01-2024 | France | Cereal | 25.26 | 28.7964 | 9 | 5 |
09-01-2024 | Brazil | Cereal | 44.575 | 50.8155 | 8 | 5 |
14-01-2024 | Brazil | Snacks | 20.185 | 4 | 5 | |
Brazil | Cosmetics | 40.485 | 46.1529 | 2 | 5 |
Note
- The COUNTX function allows you to apply multiple condition, such as counting the number of rows when the COUNTRY is "Brazil" and the CATEGORY is "Baby Food".
COUNTX([COUNTRY], [COUNTRY]="Brazil", [UNIT PRICE]>38)
-
You can apply date conditions when using the COUNTX function as below:
COUNTX([SHIP DATE], [SHIP DATE]=DATEVALUE("2024-05-05"))
Related Functions
- COUNT
- COUNTNOBLANK
- COUNTNOBLANKX
- DISTINCTCOUNT
- DISTINCTCOUNTX
- DISTINCTCOUNTNOBLANK
- DISTINCTCOUNTNOBLANKX