Most efficient formula to count records

[Mike_A]

I want to include a total record count in the titles of a number of views. I plan to add a VC in each table needed (and add that to each slice) What’s the least costly formula to count records (filter, select, count)? I don’t want to hurt sync speed if I can avoid it. Thoughts appreciated.

[Kirk_Masden]

I don’t know for certain but, based on my limited experience, I would think that filter and select would tend to be more costly than count. I’m interested in the answer to this because I’m using “count” in some virtual columns in my app to do this. I’m using

count(Slice name[Key])

In this case the slice has done what filter or select might do. Of course, if you don’t have a slice and need to pick out a subset of records to count, I think used need to use filter or select.

[Mike_A]

Thanks @Kirk_Masden. On my case I used the following in the VC
COUNT(FILTER(Activities, IN([State], {"(1) OPEN", "(2) IN-PROGRESS","(3) PAUSED"})))

But then had to take the MAX of this column in the view to get a single number. I have to take another look because there is likely a simpler way…

*My actual use case is, because the histogram chart cannot show the total record count it is based on (just shows the hover popup per bar), I want to modify the view title to shown count of the filtered record set. My approach was to add a VC, but am concerned that this has to be recalculated for every single record, each sync, rather than just when the chart view is accessed - so speed is a concern.

Other approaches appreciated!

[Aleksi]

@Mike why do you need to add the MAX for the number? The result from the COUNT expression is a number already?

[Mike_A]

Hi @Aleksi. I did this really quick last night, but without MAX, i was getting a count returned for each and every record - basically a list of counts, but all the same total count.

Looked like: 117, 117, 117, …

So, just did MAX so I got one number for count of all records. Before solving what I did wrong there, figured I would ask about the “least expensive” approach to getting a record count returned from a slice (which is actually all I want)…

[Aleksi]

You didn’t make any mistake… when you are calculating something with the virtual column, it calculates the formula for all records. IF the formula is very generic like COUNT(Table[Column]), the result is the same for all records… but still it’s just one number.

[Steve]

Just perform the count in the view’s Display Name expression. I would most definitely not do it in a virtual column attached to each row of the table. Yikes!

As for the best formula, the fastest is likely to be a raw column reference (e.g., MyTable[_ROWNUMBER]). SELECT() and FILTER() are likely to be slower.

[Mike_A]

LOL… thanks @Steve! I thought a VC might be trouble.

When you say “MyTable[_ROWNUMBER]”, can “MyTable” be a slice? I need that as the slice has a Row filter condition. I can get Activities[_ROWNUMBER] to work, but that’s the entire table.

What I really want to do is to use a slice named “Activities (Open)” as the source for COUNT like:
COUNT(Activities (Open)[_ROWNUMBER]) but that does not work (and quotes around slice name did not help).

So… is there any way to use the slice name in the view Display Name expression? Or do I need to rebuild the entire slice condition into the view Display Name expression?

Current condition is: AND([State]<>"(4) Closed", [State]<>"(5) Canceled",[ID#]<>"undefined")

UPDATE: I deleted to VC and used the following in my view Display Name expression:

“Open Activities “&”(”&
COUNT(FILTER(Activities, IN([State], {"(1) OPEN", “(2) IN-PROGRESS”,"(3) PAUSED"})))
&")"

If anyone knows a better approach, feel free to chime in!
ps: disadvantage of this approach is, I need to maintain this formula both in the slice and the view. Not the most efficient…

[Steve]

Mike:

When you say “MyTable[_ROWNUMBER]”, can “MyTable” be a slice?

Yep!

[Mike_A]

Hmmm… couldn’t get that to work with slice name Activities (open). Forget the exact error but will re-check.

Tried:
Activities (open)[_ROWNUMBER]
“Activities (open)”[_ROWNUMBER]
‘Activities (open)’[_ROWNUMBER]

Will have to try again. If the table name has spaces, does it need to be surrounded in quotes?

[Steve · Accepted solution]

The exact error might be useful.

Spaces are fine, but I don’t know about parentheses. I suspect the parens are confusing the expression parser, which likely sees activities(open), a function call, followed by [_rownumber], a column reference.

[Mike_A]

Using Activities (open)[_ROWNUMBER] as the chart view Display Name
results in the error of:
“Unable to find function ‘ACTIVITIES’, did you mean ‘INITIALS’?”

Will rename my slice and report back…

[Steve]

Bingo!

[Mike_A]

CONCLUSION: never us parenthesis in Slice names!

COUNT(Activities (open)[_ROWNUMBER]) generates an “Unable to find function” error.

COUNT(Activities_open[_ROWNUMBER]) works perfectly.

[Steve]

Heh.

I’ve reported this confusing behavior.

[Steve]

Mike:

What I really want to do is to use a slice named “Activities (Open)” as the source for COUNT like:
COUNT(Activities (Open)[_ROWNUMBER]) but that does not work (and quotes around slice name did not help).

In what way didn’t it work?