Need help to create Column based on Status value

[Anonymous]

Hi,

Please help me to derive new calculated field(MainTask -Status) in Power BI by using DAX. I have status(SubTask- Status) field which is having data at sub task(SubTask) level, based on it i have create new column(MainTask -Status) at MainTask level.

For Example, I have data like below,

MainTask      SubTask       SubTask- Status

-----------     ----------      ------------------

 A                    01                 Completed

                       02                 To Do

                       03                 In -Progress

B                     04                Completed

C                     05                To Do

D                    06                 Completed

                       07                 In -Progress

And I am expecting output results like below, That means if any of the SubTask is In-Progress & To - Do & Completed at MainTask level, then it should be in In- Progress, etc..

MainTask      MainTask -Status

-----------     -------------------

A                   In -Progress

B                   Completed

C                   To Do

D                   In -Progress

Any help is much appreciated.

Thanks,

Ganesh

[Sean · Accepted solution]

@Anonymous

So if you want another table - go to the Modeling tab - click new table and type this...

Summary Table =
SUMMARIZE (
    'Table',
    'Table'[Main Task],
    "MainTask - Status", IF (
        CALCULATE (
            DISTINCTCOUNT ( 'Table'[SubTask-Status] ),
            VALUES ( 'Table'[SubTask-Status] )
        )
            = 1,
        IF (
            SELECTEDVALUE ( 'Table'[SubTask-Status] ) = "Completed",
            "Completed",
            SELECTEDVALUE ( 'Table'[SubTask-Status] )
        ),
        "In-Progress"
    )
)

If you don't want another table but a Measure instead which you can use in a Table Visual

MainTask-Status Measure = 
IF (
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[SubTask-Status] ),
        VALUES ( 'Table'[SubTask-Status] )
    )
        = 1,
    IF (
        SELECTEDVALUE ( 'Table'[SubTask-Status] ) = "Completed",
        "Completed",
        SELECTEDVALUE ( 'Table'[SubTask-Status] )
    ),
    "In-Progress"
)

EDIT: Okay so lets also add a column in the original table

MainTask-Status Column =
IF (
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[SubTask] ),
        VALUES ( 'Table'[SubTask-Status] ),
        ALLEXCEPT ( 'Table', 'Table'[Main Task] )
    )
        = 1,
    IF (
        'Table'[SubTask-Status] = "Completed",
        "Completed",
        'Table'[SubTask-Status]
    ),
    "In Progress"
)

Hope this helps!

[Anonymous]

@Sean- It's perfectly working as expected. Thank you so much for your help.

Best Regards,

Ganesh