Counts the number of distinct values in a column while not counting any BLANK values.
|
Quick Coach Video |
What it does? Counts the number of distinct values in a column while not counting any BLANK values. Syntax DISTINCTCOUNTNOBLANK( <column>) Returns The number of distinct values in a column. |
What is the DISTINCTCOUNTNOBLANK Function?
The DISTINCTCOUNTNOBLANK function in DAX is like an enthusiastic bird watcher—it counts the unique species spotted while ignoring any blanks. This function helps you appreciate the true variety in your data without any empty entries!
Example 1: Unique Birdwatching Scores
Imagine you’re tracking the unique scores of bird species observed by different bird watchers. Here’s how your sighting data might look:
|
Bird Watcher Name |
Unique Species Count |
|
Alex |
15 |
|
Jamie |
|
|
Taylor |
10 |
|
Shawn |
15 |
|
Danielle |
8 |
To count the unique scores of species observed, ignoring any blanks, you’d use the DISTINCTCOUNTNOBLANK function like this:
Unique Birdwatching Scores =
DISTINCTCOUNTNOBLANK(BirdwatchingScores[Unique Species Count])
Total Unique Scores Calculation:
- 15 (counted)
- 10 (counted)
- 8 (counted)
- (Blank entry for Jamie is ignored)
So, how many unique birdwatching scores have been recorded? That’s a total of 3 unique scores! 🐦✨
Example 2: Unique Distances in a Marathon
Now, let’s say you want to find out how many unique distances runners completed in a marathon event. Here’s your distance data:
|
Runner Name |
Distance (miles) |
|
Gill |
26.2 |
|
Drake |
|
|
Greg |
13.1 |
|
Marshall |
26.2 |
|
Shannen |
10.0 |
|
Yash |
|
To count the unique distances completed, ignoring any blanks, you would use DISTINCTCOUNTNOBLANK like this:
Unique Distances =
DISTINCTCOUNTNOBLANK(MarathonDistances[Distance (miles)])
Total Unique Distances Calculation:
- 26.2 (counted)
- 13.1 (counted)
- 10.0 (counted)
- (Blank entries for Drake and Yash are ignored)
So, how many unique distances have been completed? That’s a total of 3 unique distances! 🏃♂️🌟
Conclusion: Count the Uniques Without Blanks!
The DISTINCTCOUNTNOBLANK function in DAX is your trusty companion for counting unique numeric values while ignoring any blanks in your dataset. Whether it’s birdwatching scores or marathon distances, DISTINCTCOUNTNOBLANK helps you get the insights you need!
So, what are you waiting for? Let’s start counting the unique values without any blanks using DISTINCTCOUNTNOBLANK and celebrate your data triumphs! 🎊🥳