Help with the DAX Calculation

[Anonymous]

Hi, 

I have a requirement to count completed projects which has sub-task and each sub-task has a status field that has value 0(pending) or 1(completed). So, a project should be count as completed only if all these tasks have the status value set as 1. I want to use this measure/column in a clustered column graph and a table that will have all other details related to the project. Any help will be appreciated. 

Thanks.

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a measure as below.

Count Complete = 
var tab = 
SUMMARIZE(
    'Table',
    'Table'[Project],
    "Flag",
    var _project = [Project]
    return
    IF(
        COUNTROWS(
            FILTER(
                ALL('Table'),
                'Table'[Project]=_project
            )
        )=
        COUNTROWS(
            FILTER(
                ALL('Table'),
                'Table'[Project]=_project&&
                'Table'[Task_Status]=1
            )
        ),1,0
    )
)
var _result = 
SUMX(
    tab,
    [Flag]
)
return
IF(
    ISBLANK(_result),
    0,
    _result
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Try

countx(filter(summarize(table, table[project],"_1" ,distinctcount(table[sub project]) , "_2" , calculated( distinctcount(table[sub project]),table[sub project Status]) = "Closed"), [_1] =[_2]),[project])

[Anonymous]

@amitchandak  Thanks for the response.

But I'm still not getting the desired output. The result has projects having both completed and pending tasks.