DAX: Return distinct column values based on other conditions

Question

I have a table whose relevant columns are: ProjectName, TaskNo, AssignedTo, and Status. The data looks something like this:

--------------------------------------------------------------
|   ProjectName   |   TaskNo   |   AssignedTo   |   Status   |
+-----------------+------------+----------------+------------+
|   Project1      |   00300    |   Database     | In Process |
+-----------------+------------+----------------+------------+
|   Project2      |   02200    |   Alfred       | In Process |
+-----------------+------------+----------------+------------+
|   Project2      |   00300    |   Database     | In Process |
+-----------------+------------+----------------+------------+
|   Project3      |   02200    |   Alfred       |   Complete |
+-----------------+------------+----------------+------------+
|   Project3      |   00900    |   Database     | In Process |
+-----------------+------------+----------------+------------+
|   Project4      |   02200    |   Alfred       |   Complete |
+-----------------+------------+----------------+------------+
|   Project4      |   01200    |   Database     |   Complete |
+-----------------+------------+----------------+------------+
|   Project4      |   00300    |   Database     |Not Started |
+-----------------+------------+----------------+------------+

There are some two dozen task numbers per project that are assigned to many different groups. I am looking to output a list of ProjectName where

TaskNo 02200 has status Complete AND There is at least 1 task assigned to Database that is not Complete.

From the table above, my desired output would be:

-------------------
|   ProjectName   |
+-----------------+
|   Project3      |
+-----------------+
|   Project4      |
-------------------

I have managed to filter the very large list down somewhat - to Completed 02200's and incomplete Database tasks with this FILTER expression:

FILTER (
'Project Tasks',
    OR (
'Project Tasks'[Title] = "02200"
&& 'Project Tasks'[Status] = "Completed",
'Project Tasks'[GroupAssignedTo] = "Database"
&& 'Project Tasks'[Status] <> "Completed"
    )
)

I can't seem to take it the rest of the way - to find within this filtered table that satisfy the two conditions.

Alexis Olson Alexis Olson · Accepted Answer · 2020-05-01T02:13:07

The trick is that you need to consider each ProjectName at a group rather than just row by row.

To do that, let's use the SUMMARIZE function:

FilteredProjects =
VAR Summary =
    SUMMARIZE (
        'Project Tasks',
        'Project Tasks'[ProjectName],
        "02200 Complete", COUNTROWS (
            FILTER (
                'Project Tasks',
                'Project Tasks'[TaskNo] = "02200"
                    && 'Project Tasks'[Status] = "Complete"
            )
        ) > 0,
        "Database Not Complete", COUNTROWS (
            FILTER (
                'Project Tasks',
                'Project Tasks'[AssignedTo] = "Database"
                    && 'Project Tasks'[Status] <> "Complete"
            )
        ) > 0
    )
RETURN
    SELECTCOLUMNS (
        FILTER ( Summary, [02200 Complete] && [Database Not Complete] ),
        "ProjectName", 'Project Tasks'[ProjectName]
    )

So for each ProjectName group, you check if that subtable has any rows that meet the specified conditions. Then you can filter for ones that meet both and pull out just the one column you want.

Here's what the Summary table looks like before it's filtered and stripped to one column:

-----------------------------------------------------------------
|   ProjectName   |   0200 Complete  |   Database Not Complete  |
+-----------------+------------------+--------------------------+
|   Project1      |   False          |   True                   |
+-----------------+------------------+--------------------------+
|   Project2      |   False          |   True                   |
+-----------------+------------------+--------------------------+
|   Project3      |   True           |   True                   |
+-----------------+------------------+--------------------------+
|   Project4      |   True           |   True                   |
-----------------------------------------------------------------

DAX: Return distinct column values based on other conditions

1 Answers