Forum Discussion

aflintdepm's avatar
aflintdepm
Helper III
3 years ago

DAX Formula to "Collect" multiple Rows

I'm trying to collect or summarize the status of tasks based on a person, but each task is a separate row.

 

Here is an example of data:

NameTask NameStatus
Bob SmithTask 1Completed
Bob SmithTask 2Pending
Jane DoeTask 1Pending
John JonesTask 1Completed
John JonesTask 2Completed

 

What I would like is a formula that says - For [Name], if any Task is Pending, then "Task Name", otherwise "Complete"

 

So, my desired results would look like this:

 

Bob Smith | "Task 2"

Jane Doe | "Task 1"

John Jones | "Complete"

 

Any examples or tutorial references would be appreciated.

Need Help
Show and Tell

2 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

    This measure works

    Measure = COALESCE(minx(FILTER(SUMMARIZE(VALUES(Data[Task Name]),Data[Task Name],"ABCD",MAX(Data[Status])),[ABCD]="Pending"),Data[Task Name]),"Complete")

     

  • Ahmedx's avatar
    Ahmedx
    Super User
    3 years ago

    pls try this

     

    Measure 1 =
COALESCE(
CALCULATE(MAX('Table'[Task Name]), 
CONTAINSSTRING("Pending",'Table'[Status])),"Completed")

------- or-----------
Measure 2 = 
CALCULATE(SELECTEDVALUE('Table'[TaskName],"Completed"),
             CONTAINSSTRING("Pending",'Table'[Status]))