Skip to content
Infoveave Help

DISTINCTCOUNTNOBLANKX

The DISTINCTCOUNTNOBLANKX function in Infoveave calculates the number of unique, distinct values in a column or dataset, while considering a specific condition or criteria defined. It counts how many different items meet the specified condition, excluding rows or items that are blank or empty.

Applicable to

  • Calculated Columns
  • Expressions

Return Value

  • The return value of the DISTINCTCOUNTNOBLANKX function is an integer representing the count of unique, non-blank values in the specified column or dataset that satisfy the specified condition.

Remark

  • DISTINCTCOUNTNOBLANKX is useful when you want to count the number of distinct items in a column or dataset while applying a specific condition or filter. It ensures that only rows or items with meaningful (non-blank) values are considered.
  • DISTINCTCOUNTNOBLANKX function can be used across Date, Strings and Numbers.
  • DISTINCTCOUNTNOBLANKX function takes only all values into account.

Syntax

DISTINCTCOUNTNOBLANKX(<expression>,<Condition>)

DISTINCTCOUNTNOBLANKX in Board Expression

ParameterDescription
ExpressionA placeholder in a function that is replaced with the actual widget and measure names.
ConditionA placeholder in a function that is replaced with the condition filter.
Widget NameThe specific name or identifier of the widget being used for data visualization in the specific Infoboard.
MeasureRepresents the name of the measure that is being displayed or analyzed using the widget.
DimensionRepresents the dimension column that is being displayed or analyzed using the widget.
Dimension ValueRepresents the specific dimension items within the dimension column used for the visualization in the widget.

Steps to Use DISTINCTCOUNTNOBLANKX

  1. Write the DISTINCTCOUNTNOBLANKX function. For instance DISTINCTCOUNTNOBLANKX(<expression>,<condition>)
  2. Replace <expression> with 'Widget Name'[Measure] and <condition> with [Dimension Name]="Dimension Value". Replace Widget Name, Measure, Dimension, and Dimension Value with your actual values.
  3. Set the DISTINCTCOUNTNOBLANKX function based on the scenario.
  4. To learn how to configure an Expression in Infoveave, visit the section Configure Expression.

Example

Scenario

  • You are managing a dataset and an associated Infoboard named ‘Finance Customers’ within your Finance Management System. Your primary objective is to monitor the eligibility of finance customers for discounts. Specifically, you want to count the number of unique finance customers who belong to either the “Pay on Time” or “No Discount” category. Moreover, you aim to ensure that this count remains less than or equal to 100. If the count of eligible customers drops to 100 or below, you intend to trigger a specific action on your dashboard.

Scenario Details

  • Dashboard Name Finance Management Dashboard
  • Widget Name Finance Customers
  • Measure Name Customers (representing the number of customers)
  • Dimension Name Discount Types (categorizing customers based on their payment behavior)
  • Dimension Values “Pay on Time” (customers who pay their bills on time) and “No Discount” (customers not eligible for any discount)

Objective

Your objective is to count the number of unique solar customers who belong to either the “Pay on Time” or “No Discount” category, and you want to ensure this count is less than or equal to 100 to trigger a specific action on your dashboard.

  • You can use the DISTINCTCOUNTNONBLANKX function like this
DISTINCTCOUNTNONBLANKX('Finance Customers'[Total Customers], [Discount Types] = "Pay on Time" OR [Discount Types] = "No Discount") <= 100

DISTINCTCOUNTNOBLANKX in Calculated Columns

ParameterDescription
ExpressionA placeholder in a function that is replaced with the column names.
ConditionA placeholder in a function that is replaced with the condition filter.
Column NameThe name of the column in the dataset or Datasource that contains the values you want to analyze.
Filter ConditionA 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.

Steps to Use DISTINCTCOUNTNOBLANKX

  1. Write the DISTINCTCOUNTNOBLANKX function. For instance DISTINCTCOUNTNOBLANKX(<expression>,<condition>)
  2. Replace <expression> with [Column Name] and <condition> with [Column Name]="Column Value". Replace Column Name with the required column name in the dataset.
  3. To learn how to use Infoveave functions in calculated columns, visit Add Calculated Column under Sources.

Example

Objective Consider that you have the below sales dataset, your goal is to count the distinct number of rows in the dataset where COUNTRY is BRAZIL and CATEGORY is BABY FOOD, excluding null or blanks.

ORDER DATECOUNTRYCATEGORYUNIT PRICEMARKET PRICEQUANTITY
2024-01-03BrazilBaby Food38.4143.78742
2024-01-07JapanSpices45.5650.815520
Japan43.787410
2024-01-1832.30194
2024-01-22BrazilCosmetics28.33523.01097
2024-01-26Canada20.18523.01099
2024-01-04FranceCereal25.2628.79649
2024-01-09BrazilCereal44.57550.81558
2024-01-14BrazilSnacks20.1854
BrazilCosmetics40.48546.15292
  • You can use the DISTINCTCOUNTNOBLANKX function like
DISTINCTCOUNTNOBLANKX([COUNTRY],[COUNTRY]="Brazil" AND [CATEGORY]="Baby Food")

The new calculated column will return the total number of rows that met the condition as 1

ORDER DATECOUNTRYCATEGORYUNIT PRICEMARKET PRICEQUANTITYCOUNTRY COUNT
03-01-2024BrazilBaby Food38.4143.787421
07-01-2024JapanSpices45.5650.8155201
Japan43.7874101
18-01-202432.301941
22-01-2024BrazilCosmetics28.33523.010971
26-01-2024Canada20.18523.010991
04-01-2024FranceCereal25.2628.796491
09-01-2024BrazilCereal44.57550.815581
14-01-2024BrazilSnacks20.18541
BrazilCosmetics40.48546.152921