Forum Discussion
Conditional Distinct Count of Values
In a working Deneb visual that displays survey data, I have a relatively flat dataset structured this way:
When aggregating this data, I want a count of distinct values for "person" - ONLY for rows where "complete" = 0. Also, I do not want to filter the data to achieve this, because I need the filtered rows later for other purposes. My first strategy was to calculate a new column called "distinctPerson" that listed the person column value if "complete" == 1 and showed null otherwise, but the "distinct" count sees 'null' as a distinct value rather than ignoring it. Here is a link to the example in the vega editor. As you can see from the chart, "null" values - which are cases where there is no value in the person column because "complete" = 0 - are counted as distinct values:
How can I remove these null values from the count (not the chart, the actual aggregation in the background) without filtering the dataset to "datum.Completed == 1" since this would remove necessary information for later? I have tried the following concepts without success:
- Perform transforms within a lookup (this doesn't seem to work - maybe I'm missing something?)
- Define and transform an additional data object that references my initial named dataset and join onto original (I haven't figured out a way to get this working)
If I'm missing something major, please let me know - or if one of the two failed attempts I listed is likely to work, and I'm just using the wrong syntax, let me know and I can provide more info.
Thanks in advance!
UPDATE: pbix containning the visual and dataset here.
I found a simple solution. I used a "missing" op in my aggregation step to detect when null values were present in the data, and then subtracted 1 from the value of a "distinct" aggregation in a later calculate step whenever a null value was present in the series. Sample chart and dataset available at this link in the Vega editor.
Chart below:
3 Replies
Can you share the sample dataset?
Link to pbix file with sample dataset and chart here.
I found a simple solution. I used a "missing" op in my aggregation step to detect when null values were present in the data, and then subtracted 1 from the value of a "distinct" aggregation in a later calculate step whenever a null value was present in the series. Sample chart and dataset available at this link in the Vega editor.
Chart below:
