cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Need help to create Column based on Status value

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: Need help to create Column based on Status value

@Ganesh365

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! Smiley Happy

View solution in original post

2 REPLIES 2
Highlighted
Community Champion
Community Champion

Re: Need help to create Column based on Status value

@Ganesh365

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! Smiley Happy

View solution in original post

Highlighted
Helper I
Helper I

Re: Need help to create Column based on Status value

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

 

Best Regards,

Ganesh

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors