Count if all tasks status complete

[izzleee]

Here is my attached sample file.

hasonevalue.pbix (22.0 KB)

I’m trying to check if a person has completed all of their training. My method was to summarize and filter the table then use Hasonevalue and “complete” status. See below for screen shot. Note ian, Jani, kieran and tom have completed everything whereas the other names have Incomplete:

My question is pretty much identical to this PowerBI forum which is solved. I can’t seem to use the same Earlier function like the solution though. I also have treid HasOneValue() but not sure of most suitable check here.

Thanks as always for your help.

[Harsh]

Hello @izzleee,

Thank You for posting your query onto the Forum and for providing the reference link as well as for clear and concise explanation about the results that you’re trying to achieve.

You can use the below provided formula to create a column in your table -

Column = 
VAR _Table = 
SUMMARIZE( 
    FILTER( Table_summarize ,
        Table_summarize[Name] = EARLIER( Table_summarize[Name] ) ) ,
    Table_summarize[Status] )

RETURN
IF(
    MAXX( _Table , Table_summarize[Status] ) = "Complete" && 
    COUNTROWS( _Table ) = 1 , 
    1 , 
    0
)

Below is the screenshot of the results provided for the reference -

I’m also attaching the working of the PBIX file for the reference.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

hasonevalue - Harsh.pbix (22.6 KB)

[izzleee]

Thanks Harsh,

Do you know if there is way to do this as a measure or virtual table? I couldn’t use Earlier in Summarized table except if I used a calculated column?

[Anurag]

Hi ,
You can use the below measure for your requirement attaching the pbix for your reference.

dcount =
VAR tb =
SUMMARIZE ( Group_sample, Group_sample[Name], Group_sample[Status] )
VAR ad =
ADDCOLUMNS (
tb,
“Cou”,
VAR nam = [Name]
VAR Step1 =
COUNTROWS ( FILTER ( tb, [Name] = nam ) ) --VAR step2=COUNTX(step1
VAR step2 =
IF ( Step1 = 1 && [Status] = “Complete”, 1, 0 )
RETURN
step2
)
RETURN
SUMX ( ad, [Cou] )

Hope this will help you.

Thanks,
Anurag
hasonevalue.pbix (24.0 KB)