Hello! I'm attempting to create a report that shows a list of projects that do not have a specific participation user_id assigned. I've tried a few things and am running into a wall. Does anyone have any experience with anything like this?

Hi Brent, are the missing/non existing values for user_id represented as null? If so, it would be easy to apply a filter to the insight usign the attribute in question to show null values only. If user_id is stored as a text value and empty strings (

""

) represent unassigned values, you might just need to add

WHERE {Participation user_id} = ""

. It is also possible to create an SQL dataset where you could pre-calculate this and add the projects without a user_id as a standalone attribute, e.g.:

SELECT project_name
FROM projects p
WHERE NOT EXISTS (
    SELECT 1
    FROM participation pa
    WHERE pa.project_id = p.project_id
      AND pa.user_id IS NOT NULL
);

Hi Brent, could you share how the projects and participation user IDs are structured in your data model? Are they in the same dataset or in somehow connected datasets?

Hi @Michal Hauzirek, I have projects in the projects table, and the participation user ids in the project participations table. I believe these are connected via the project id. As an example, I have around 500 distinct projects, each with multiple participation ids, and each of those ids has a user id associated to it. We'll say the user id I'm interested is "12345". I want to see a list of all projects that do not have a project participation with user id "12345" (all project participations will have some user id, I just want to see the projects that do not have this particular user id associated. If that's not possible with MAQL, another option might be to see a list of all projects with the sum of count of all project participation user ids that equal "12345", and from there I could filter down to show only those that have a sum/count of zero.

Hi Brett, sorry about the radio silence. Could you also specify what is the issue with the model if any? Are you unable to apply a filter for the ID's tied to null values?