Skip to content
Infoveave Help

DISTINCTCOUNTNOBLANK

The DISTINCTCOUNTNOBLANK function in Infoveave calculates the number of unique and distinct values in a column or dataset, while excluding items that are blank or empty.

Applicable to

  • Calculated Columns
  • Expressions

Return Value

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

Remark

  • DISTINCTCOUNTNOBLANK is useful when you want to count the number of distinct items in a column or dataset, but you want to exclude blank values.
  • DISTINCTCOUNTNOBLANK function can be used across Date, Strings and Numbers.
  • DISTINCTCOUNTNOBLANK function takes all non-blank values into account.

Syntax

DISTINCTCOUNTNOBLANK(<expression>)

DISTINCTCOUNTNOBLANK in Board Expression

ParameterDescription
ExpressionA placeholder in a function that is replaced with the actual widget and measure names.
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.

Steps to Use DISTINCTCOUNTNOBLANK

  1. Write the DISTINCTCOUNTNOBLANK function. For instance DISTINCTCOUNTNOBLANK(<expression>)
  2. Replace <expression> with 'Widget Name'[Measure]. Replace Widget Name with the actual name of your widget and Measure with the corresponding measure name.
  3. Set the condition with the DISTINCTCOUNTNOBLANK function based on the scenario. For instance, you can set the count entries that meet a certain criterion, as the condition.
  4. To learn how to configure an Expression in Infoveave, visit the section Configure Expression.

Scenario You’re overseeing a dataset and an associated Infoboard named ‘Solar Feed In’ within your Energy Consumption Analysis System. Your primary goal is to maintain data quality by accurately counting the number of unique solar customers who have meaningful data entries (non-blank) in the dataset. You want to ensure that this count remains below a threshold of 20. If the count of unique solar customers with non-blank entries exceeds this limit, you need to trigger a specific action on your dashboard.

Scenario Details

  • Dashboard Name Energy Consumption Analysis Dashboard
  • Widget Name Solar In
  • Measure Name Solar Customers (representing the number of solar 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 have meaningful data entries (non-blank) in the dataset, and you want to ensure this count is less than 20 to trigger a specific action on your dashboard.

  • You can use the DISTINCTCOUNTNOBLANK function like this
DISTINCTCOUNTNOBLANK('Solar In'[Solar Customers])>20

DISTINCTCOUNTNOBLANK in Calculated Columns

ParameterDescription
ExpressionA placeholder in a function that is replaced with the column name.
Column NameThe name of the column in the dataset or Datasource that contains the values you want to analyze.

Steps to Use DISTINCTCOUNTNOBLANK in Calculated Columns

  1. Write the DISTINCTCOUNTNOBLANK function. For instance DISTINCTCOUNTNOBLANK(<expression>)
  2. Replace <expression> with [Column Name] in the dataset.
  3. To learn how to use Infoveave functions in calculated columns, visit Add Calculated Column under Sources.

Objective Consider that you have the below sales dataset, your goal is to get the distinct count countries without considering the blank values.

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 DISTINCTCOUNTNOBLANK function like this

DISTINCTCOUNTNOBLANK([COUNTRY])

The new calculated column “Total Entries” will return the total number of distinct count of Countries in the dataset avoiding nulls and blanks as 4.

ORDER DATECOUNTRYCATEGORYUNIT PRICEMARKET PRICEQUANTITYTOTAL ENTRIES
03-01-2024BrazilBaby Food38.4143.787424
07-01-2024JapanSpices45.5650.8155204
Japan43.7874104
18-01-202432.301944
22-01-2024BrazilCosmetics28.33523.010974
26-01-2024Canada20.18523.010994
04-01-2024FranceCereal25.2628.796494
09-01-2024BrazilCereal44.57550.815584
14-01-2024BrazilSnacks20.18544
BrazilCosmetics40.48546.152924