How to retrieve one value from multiple matching records

[Rajat]

Project Master                                                                                         Task Allocation

ProjectID  projectName  Project Status                                                 Task ID   ProjectiD  Task status

  P1            A                                                                                           T1          P1           Completed

  P2            B                                                                                           T2          P1            In progress

  P3            C                                                                                           T3          P1            Completed

                                                                                                                T4          P2           Inprogress

                                                                                                                T5          P2           Completed

                                                                                                                T6          P2           In progress

I need to update "Project status" in Project Master in such a way that if any of the task of Project is in progress then we need to update "Project status"  as "In progress" othervise "Completed".

Can any one tell me, how to aaprach the problem.

I tried it with LookupValue(),but is works only for one matching record.But here are multiple matching record.

[kazlik]

How many Task status types do you have?

[greggyb]

// DAX
// Calculated Column
ProjectStatus =
IF(
    ISEMPTY(
        CALCULATETABLE(
            'Task Allocation'
            ,'Task Allocation'[ProjectID] = EARLIER( 'Project Master'[ProjectID] )
            ,'Task Allocation'[Task Status] = "In Progress"
        )
    )
    ,"Completed"
    ,"In Progress"
)

ISEMPTY() checks whether a table has 0 rows or >0 rows (true for 0).

CALCULATETABLE() evaluates a table in the modified filter context defined in its arguments 2-N. We evaluate 'Task Allocation' where [ProjectID] is the same as on the current row in 'Project Master' (EARLIER() jumps us out to the row context in 'Project MAster' ), and we only want rows where the status is "In Progress".

This CALCULATETABLE() returns all associated rows in 'Task Allocation' that are in progress. If there are none, then ISEMPTY() returns true, and we return "Completed" from our IF(). If there *are* rows with [Task Status] = "In Progress", then ISEMPTY() returns false and our IF() returns "In progress".

[kazlik]

Can also use something like this but suspect Gregg's is a better option.

=IF(CALCULATE(COUNTAX(FILTER(Sheet2,Sheet2[Task status]="In Progress"),Sheet2[Task ID]),FILTER(Sheet2,Sheet2[ProjectiD] = Sheet1[ProjectID])) <> BLANK(),"In Progress", "Completed")

[greggyb]

@kazlik, a few things:

• DAX Formatter
• Code blocks in the forum reply editor for legibility
• You've got a doubly nested iterator in your counting portion - COUNTAX() will iterate over every row in its input table, and is a formula engine function, which means it will force single threaded execution
  • Within the COUNTAX() is a FILTER(), which is similarly a single-threaded, formula engine, row-by-row iterator
  • This combination means that your worst case performance is O(N^2) in the size of the 'Task Allocation' table
• Testing any count against 0 is a code smell - a count is exhaustive, whereas an ISEMPTY() or ISBLANK() existence check can terminate early. ISEMPTY() can terminate as soon as it finds a single row. A count must traverse the whole table before returning

[kazlik]

Thanks Gregg for the insight in this.