Build DAX measure and calculated column to determine project status

[WWC2024]

Hi,

I am new power bi user..... Could you please show me how to build DAX measure and calculated column to determine project's final status. Thanks. 

[v-veshwara-msft]

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.

[WWC2024]

Hi,v-vwshwara-msft

I used the concept you provided and made some modifications shown below.It works now. Thanks so much 

FinalStatusMeasure 2 =

var _1 = countx(filter(ProjectTasks, ProjectTasks[project] = earlier(ProjectTasks[project]) && [task status] = "executed-k"),ProjectTasks[project])

var _2 = countx(filter(ProjectTasks, ProjectTasks[project] = earlier(ProjectTasks[project]) && [task status] = "executed-nk"),ProjectTasks[project])

var _3 = countx(filter(ProjectTasks, ProjectTasks[project] = earlier(ProjectTasks[project]) && [task status] = "unexecuted-k"),ProjectTasks[project])

return

SWITCH(

    TRUE(),

    NOT ISBLANK(_1),"completed",

    NOT ISBLANK(_2) && ISBLANK(_3),"completed" ,

    "imcompleted")

[WWC2024]

Hi, Thanks for the reply..... 

the logix used is right but I noticed an inconsistency in the status of Project 1. The final status should be marked as "Completed," but it currently shows both "Completed" and "Incomplete." Could you please take a look ? 
Thanks

[v-veshwara-msft · Accepted solution]

Hi @WWC2024 ,

Thanks so much for the follow-up,
Glad to hear the logic made sense and that you were able to tweak it to fit your needs.

Regarding the inconsistency you noticed with Project 1,
That usually happens when a measure evaluates row by row instead of checking all tasks related to the project.
To fix this I have updated the DAX measure to make sure it always checks all tasks for the project regardless of how the visual is filtered,

Updated Final Status Measure:

FinalStatusMeasure = 
VAR ProjectID = SELECTEDVALUE(ProjectTasks[project])

VAR HasExecutedK = 
    CALCULATE(
        COUNTROWS(ProjectTasks),
        REMOVEFILTERS(ProjectTasks),
        ProjectTasks[project] = ProjectID,
        ProjectTasks[task status] = "executed-k"
    ) > 0

VAR HasExecutedNK = 
    CALCULATE(
        COUNTROWS(ProjectTasks),
        REMOVEFILTERS(ProjectTasks),
        ProjectTasks[project] = ProjectID,
        ProjectTasks[task status] = "executed-nk"
    ) > 0

VAR HasUnexecutedK = 
    CALCULATE(
        COUNTROWS(ProjectTasks),
        REMOVEFILTERS(ProjectTasks),
        ProjectTasks[project] = ProjectID,
        ProjectTasks[task status] = "unexecuted-k"
    ) > 0

RETURN
    IF(
        HasExecutedK,
        "completed",
        IF(
            HasExecutedNK && NOT HasUnexecutedK,
            "completed",
            "incompleted"
        )
    )

By using REMOVEFILTERS we make sure DAX looks at all the tasks for the project not just the current row
That way the status remains consistent throughout the report

I have attached the updated PBIX file where both the corrected measure and calculated column are included.
Feel free to explore it.

Hope this helps.
Please consider marking any helpful reply, including your own, as the Accepted Solution to assist other community members in finding the answer more easily.
Thank you.

[WWC2024]

It works this time....Thanks you do much.....