Is there a way to run count over two columns? Basically, I have two columns of strings, and I want to count the number of unique strings that exist when both columns are combined.

Hi Daniel, it is not possible directly with the COUNT function, but there might be a way how to achieve this in GoodData MAQL. Let’s assume your dataset has columns

Col1, Col2, Col3, Col4

and you want to get distinct count of combination of

Col2

and

Col3

You can write this metric:

SELECT SUM( (SELECT 1 BY *Col2*, *Col3*) )

This first in the inner metric aggregates the data by

col2

and

col3

and assigns number 1 to each such row. Then in the outer metric it just simply sums all the ones. This metric should even automatically work well if you for example slice it by

Col4

in your insight. It should give you the number of distinct values of

col2+col3

combined for each value of

col4

Actually, I'm running into a problem with this metric. Essentially, I have an attribute filter on the entire dashboard for

col4

but it doesn't seem to be working with the

SELECT SUM( (SELECT 1 BY *Col2*, *Col3*) )

metric.

I see, I guess that might be some internal optimization because we used the constant

1

which is not from that particular dataset and therefore unrelated to

col4

. In that case I believe this modification should make it work even with the filter:

SELECT SUM((SELECT SIGN(COUNT(*ID*) BY Col2, Col3))

Here we replaced the static

1

with

SIGN(COUNT(ID))

where ID is a primary key of your dataset (if there is one or the “`Records of …`” in the GD Platform if you do not have a single column primary key). Technically it will still return 1, but now “from the dataset” which should ensure the filter is applied properly. Now calculate number of records (which is always positive), then apply SIGNUM function (which turns any positive number to 1) and the rest is the same.

That worked. I really appreciate it! I'll be back with more questions soon!