Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity 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)