Build DAX measure and calculated column to determine project status

Hi WWC2024 ,
Thanks for using Microsoft Fabric Community,

 

To determine a project's final status based on the task statuses, you can use a calculated column. This is a great starting point as it adds the result to each row, which is helpful when learning how DAX logic works.

 

Logic Used:

If any task is "executed-k" -> the project is completed.

Else if there’s at least one "executed-nk" and no "unexecuted-k" -> the project is also completed.

In all other cases -> the project is incompleted.

 

Calculated Column:(Go to Modeling-> New Column)

FinalStatusColumn = 
-- Step 1: Get the project ID from the current row
VAR ProjectID = ProjectTasks[project]

-- Step 2: Check if the project has any task marked "executed-k"
VAR HasExecutedK = 
    CALCULATE(
        COUNTROWS(ProjectTasks),
        ProjectTasks[project] = ProjectID,
        ProjectTasks[task status] = "executed-k"
    ) > 0

-- Step 3: Check if the project has any task marked "executed-nk"
VAR HasExecutedNK = 
    CALCULATE(
        COUNTROWS(ProjectTasks),
        ProjectTasks[project] = ProjectID,
        ProjectTasks[task status] = "executed-nk"
    ) > 0

-- Step 4: Check if the project has any task marked "unexecuted-k"
VAR HasUnexecutedK = 
    CALCULATE(
        COUNTROWS(ProjectTasks),
        ProjectTasks[project] = ProjectID,
        ProjectTasks[task status] = "unexecuted-k"
    ) > 0

-- Step 5: Apply logic based on the conditions
RETURN
    IF(
        HasExecutedK,
        "completed",                             -- Rule 1 met
        IF(
            HasExecutedNK && NOT HasUnexecutedK, -- Rule 2 met
            "completed",
            "incompleted"                        -- Otherwise
        )
    )

Output:

 

Calculated Column vs. Measure:
Calculated Column is evaluated for each row in the table and becomes a part of your data model. It's useful when you want to display the result at a detailed level (e.g. per task).

Measure is calculated dynamically at the time of visualization, based on filter context. It's best when summarizing data, e.g. showing one status per project.

 

I’ve also included a sample .pbix file with the dataset, where both the calculated column and the corresponding measure are implemented for your reference. Feel free to explore and adjust as needed.

Please reach out for further assistance.
If this post helps, then please consider "Accept it as the solution" to help the other members find it more quickly and a kudos would be appreciated.