Project status report based on subtasks status

[margheritamart]

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 · Accepted solution]

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

[margheritamart]

Thank you!!!

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

[johnt75]

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

[johnt75]

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" )