Forum Discussion
Anonymous
6 years agoNot applicable
How to return distinct values from 1 column based on multiple conditions
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 my table somewhat with
FILTER (
'Project Tasks',
OR (
'Project Tasks'[Title] = "02200"
&& 'Project Tasks'[Status] = "Completed",
'Project Tasks'[GroupAssignedTo] = "Database"
&& 'Project Tasks'[Status] <> "Completed"
)
)
But cannot figure out how to take it the rest of the way. Any help would be greatly appreciated!
Perhaps:
Table = VAR __Table = SUMMARIZE( 'Table', [ProjectName], "02200",COUNTROWS(FILTER('Table',[TaskNo] = "02200" && [Status] = "Complete")) "Database",COUNTROWS(FILTER('Table',[AssignedTo] = "Database" && [Status] <> "Complete")) RETURN FILTER(__Table,[02200] > 0 && [Database] > 0)
1 Reply
- Greg_Deckler
Community Champion
Perhaps:
Table = VAR __Table = SUMMARIZE( 'Table', [ProjectName], "02200",COUNTROWS(FILTER('Table',[TaskNo] = "02200" && [Status] = "Complete")) "Database",COUNTROWS(FILTER('Table',[AssignedTo] = "Database" && [Status] <> "Complete")) RETURN FILTER(__Table,[02200] > 0 && [Database] > 0)