Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Help with the DAX Calculation

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.

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    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.

     

     

3 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    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's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      amitchandak  Thanks for the response.

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