How to implements DistinctIf() functioin

Get Help
1

Is there a way to implement 'distinctIf()' function, like the bigquery "count(distinct(if(...)))".

-- for BigQuery
SELECT 
    COUNT(DISTINCT(IF(c2 < 4, c1, NULL))) total
    , COUNT(DISTINCT(IF(c3 > 5, c1, NULL))) total
FROM (
  SELECT 'a' AS c1, 1 AS c2, 4 AS c3
  UNION ALL
  SELECT 'a' AS c1, 3 AS c2, 9 AS c3
  UNION ALL
  SELECT 'c' AS c1, 5 AS c2, 10 AS c3
) AS table_1

Or how to assign NULL value in the custom expression?

I could use Distinct(CASE(c2 < 4, c1, '')) to implement it. But don't know how to assign "NULL" value in the custom expression. It would cause the result one more than the actual count.

2

Hi @OliverWey
Don't include , "", then it will null:
Distinct(case([c2] < 4, [c1]))

3

It works. Thanks