Forum Discussion

margheritamart's avatar
margheritamart
Frequent Visitor
4 years ago
Solved

Project status report based on subtasks status

Hi all,

 

I am trying to create a calculated status for projects that are currently in progress based on the status of their subtasks which are stored in a separate table called 'Items'. If the status of all items is set as "Delivered" or "Invoiced" and the project status is "In progress", I would like to display in the report: "complete project". 

Tables relationship:  Items ->Orders 

 

Thanks for your help!!

 

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi margheritamart ,

    I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a calculated column as below:

    Column = 
VAR _pitemcount =
    CALCULATE (
        DISTINCTCOUNT ( 'Items'[Item] ),
        FILTER (
            ALLSELECTED ( 'Items' ),
            'Items'[Project ID] = EARLIER ( 'Orders'[Project ID] )
        )
    )
VAR _statuscount =
    CALCULATE (
        DISTINCTCOUNT ( 'Items'[Item] ),
        FILTER (
            ALLSELECTED ( 'Items' ),
            'Items'[Project ID] = EARLIER ( 'Orders'[Project ID] )
                && 'Items'[Status] IN { "Delivered", "Invoiced" }
        )
    )
RETURN
    IF (
        _pitemcount = _statuscount
            && 'Orders'[Status] = "In progress",
        "complete project",
        'Orders'[Status]
    )

    If the above one can't help you get the desired result, please provide some sample data in the table Items and Orders table and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

    How to upload PBI in Community

    Best Regards

4 Replies

  • johnt75's avatar
    johnt75
    Super User
    4 years ago

    You could create a calculate column on the Orders table as

     

    Project complete = IF( 'Orders'[status] = "In progress" && 
  ISEMPTY( FILTER( RELATEDTABLE('Items'), NOT 'Item[status] IN { "Delivered", "Invoiced" })),
"complete project", "not complete project" )

     

  • margheritamart's avatar
    margheritamart
    Frequent Visitor
    4 years ago

    Thank you!!!

    I have tried but I get the error the syntax of NOT is incorrect.

    • johnt75's avatar
      johnt75
      Super User
      4 years ago

      My mistake, I've edited my original post to correct it

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi margheritamart ,

    I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a calculated column as below:

    Column = 
VAR _pitemcount =
    CALCULATE (
        DISTINCTCOUNT ( 'Items'[Item] ),
        FILTER (
            ALLSELECTED ( 'Items' ),
            'Items'[Project ID] = EARLIER ( 'Orders'[Project ID] )
        )
    )
VAR _statuscount =
    CALCULATE (
        DISTINCTCOUNT ( 'Items'[Item] ),
        FILTER (
            ALLSELECTED ( 'Items' ),
            'Items'[Project ID] = EARLIER ( 'Orders'[Project ID] )
                && 'Items'[Status] IN { "Delivered", "Invoiced" }
        )
    )
RETURN
    IF (
        _pitemcount = _statuscount
            && 'Orders'[Status] = "In progress",
        "complete project",
        'Orders'[Status]
    )

    If the above one can't help you get the desired result, please provide some sample data in the table Items and Orders table and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

    How to upload PBI in Community

    Best Regards