Hi Team We have a customer trying to build a compliance % metric in MAQL. They need to count each course once per user when a course can be tasked multiple times. Their attempt with

COUNT(DISTINCT {label/content.type_with_id})

fails with "No definition for function 'DISTINCT'". From the docs, our understanding is that MAQL's

COUNT

already performs distinct counting by default — is that correct? If so, would

SELECT COUNT({label/content.type_with_id})

already return unique course values per user when sliced by a user attribute? The second issue is filtering to only the latest task per user+course combination (by

assigned_at

date). We attempted a correlated subquery approach using

MAX(assigned_at)

but the report returns "No values to display" despite data existing. Is there a supported MAQL pattern for this, or does it require adding a pre-computed field (e.g.

is_latest_task

) at the dataset/ETL level? Thanks

Hi, Great questions — let me address both parts. 1. Does MAQL COUNT already perform distinct counting? Yes, that's correct. MAQL's

COUNT

function returns the number of unique values for an attribute by default — there is no separate

DISTINCT

keyword, which is why

COUNT(DISTINCT ...)

fails. [COUNT] So

SELECT COUNT({label/content.type_with_id})

will already count unique course values. When this metric is used in a visualization sliced by a user attribute, it will return the count of unique courses per user — which is the behaviour your customer needs. However, be aware of context ambiguity: if

content.type_with_id

is connected to multiple fact datasets in the LDM, the single-parameter

COUNT

may resolve ambiguously and join counts from multiple contexts. If that happens, use the two-parameter form or

USING

to pin the count to the correct dataset:

SELECT COUNT({label/content.type_with_id}, {dataset/your_fact_dataset})

or

SELECT COUNT({label/content.type_with_id}) USING {attribute/your_primary_key}

[COUNT] 2. Filtering to the latest task per user+course combination The supported MAQL pattern for filtering to the maximum date is a

WHERE

clause using a

MAX

submetric. The general form from the documentation is:

SELECT SUM({fact/snapshot_value}) 
WHERE {attribute/snapshot_date.day} =
      (SELECT MAX({attribute/snapshot_date.day},{dataset/fact_dataset}))

[MAX] You would adapt this to filter your compliance metric to only rows where

assigned_at

equals the maximum

assigned_at

for the current context (i.e., per user+course slice). For example:

SELECT COUNT({label/content.type_with_id})
WHERE {label/assigned_at.day} =
      (SELECT MAX({label/assigned_at.day}, {dataset/your_fact_dataset}))

Important caveat on

MAX

with date attributes: When the metric is broken down by multiple attributes in a table, the

MAX

subquery resolves per row by default, which may give the correct per-user+course maximum. However, if you need the maximum to be fixed across all rows regardless of slicing, add `BY ALL OTHER`:

SELECT MAX({label/assigned_at.day}, {dataset/your_fact_dataset}) BY ALL OTHER

[MAX] Regarding the "No values to display" issue with your correlated subquery attempt — without seeing the exact metric definition it's hard to diagnose, but common causes include: • The date attribute being referenced from multiple datasets (requiring the two-parameter

MAX

form with an explicit dataset). • A mismatch between the granularity of the

assigned_at

attribute used in the

WHERE

clause and the one used in the

MAX

subquery (e.g., mixing

.day

and a full datetime label). On the ETL/pre-computed field approach: Based on community experience with similar filtering challenges, adding a pre-computed flag (like

is_latest_task

) at the ETL/dataset level is a reliable fallback when MAQL subquery filtering proves difficult to get right. [community thread] The knowledge sources do not explicitly confirm whether the correlated

MAX

subquery approach fully supports the user+course partitioning you need, so if the MAQL approach continues to return no data, the ETL flag may be the more robust solution. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups. 👀 Our Support team monitors all messages and will jump in if needed.

Hi Jhonata. Please follow the steps from our AI, and if that doesn't help, please provide more information, such as screenshots, metric names, and your workspace IDs. If you want to share it privately, you can ask about it in this thread, and one of our specialists will contact you via DM.