Conditional Distinct Count of Values

sean_cochran — Thu, 12 Sep 2024 16:39:59 GMT

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!

@giammariam

UPDATE: pbix containning the visual and dataset here.

Re: Conditional Distinct Count of Values

lbendlin — Thu, 12 Sep 2024 14:17:57 GMT

Can you share the sample dataset?

Re: Conditional Distinct Count of Values

sean_cochran — Thu, 12 Sep 2024 15:31:23 GMT

Link to pbix file with sample dataset and chart here.

Re: Conditional Distinct Count of Values

sean_cochran — Fri, 13 Sep 2024 22:05:19 GMT

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:

topic Re: Conditional Distinct Count of Values in Custom Visuals Development Discussion

Conditional Distinct Count of Values

Re: Conditional Distinct Count of Values

Re: Conditional Distinct Count of Values

Re: Conditional Distinct Count of Values