I am creating a LookML dashboard which requires some measures that are calculating a COUNT_DISTINCT on two fields from a table. From my understanding, COUNT_DISTINCT should be able to take in 2 arguments but I keep getting the error that is attached. I have reached out to Looker Support numerous times but they do not seem to have an answer. They have created test measures using 2 fields as arguments in COUNT_DISTINCT but did not have any issues. Has anyone else run into this problem?
You didn't mention which Database you are using, but Standard SQL does not allow two arguments in count distinct
The below would end up in error, So it is not a problem with Looker/LookML.
select count(distinct col1, col2) from table.
If you want to count distinct on two columns you can use double pipe or Concat function depends on your database.
measure: test_count_distinct {
type: count_distinct
sql: ${TABLE}.column1 || ${TABLE}.column2 ;;
}
Apologies. I am using BQ Standard SQL. I have tried many iterations of your proposed solution (using CONCAT, pipe, and event taking out DISTINCT) but still am seeming to have the same error.
@tmordan If it is a Standard BigQuery SQL, double pipe will work, I checked it on my end. I think there might be a minor details that trips you. Would be able to post the entire SQL ( You can remove the table name) here ?