Counting Rows using condition for more than one column

[Anonymous]

Hi

I've the following data that tracks how many users completed their training.

The system that tracks training checks everytime the user access the training material how much is completed.

From the table below, number of people who completed the training should be:

Training 1:  3 (user 1, user 3, User 4)

Training 2: 1 (user 1)

From the table below, number of people who did NOTcompleted the training should be:

Training 1:  1 (user 2)

Training 2: 3 (users 2,3,4)

How can I make a way so that I can count the number of users who didn't complete the training. If I put a condition that % complete is < 100%, then it will count users who completed that training.

I want a way to count the rows using a condition that % complete is < 100% and not to count the rows when the user already has 100% completion for the same training.

User Email	Training Title	% Complete
user1@company.com	Training 1	30%
user1@company.com	Training 1	100%
user2@company.com	Training 1	30%
user3@company.com	Training 1	100%
user3@company.com	Training 1	60%
user4@company.com	Training 1	100%
user1@company.com	Training 2	100%
user2@company.com	Training 2	30%
user3@company.com	Training 2	20%
user4@company.com	Training 2	75%

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, you may create two measures as below. The pbix file is attached in the end.

CountCompleted = 
var _training = SELECTEDVALUE('Table'[Training Title])
var tab = 
SUMMARIZE(
    'Table',
    'Table'[Training Title],
    "flag",
    var newtab =
        ADDCOLUMNS(
            DISTINCT('Table'[User Email]),
            "flag",
            IF(
                CALCULATE(
                    MAX('Table'[% Complete]),
                    FILTER(
                        ALL('Table'),
                        'Table'[Training Title] = EARLIER('Table'[Training Title])&&
                        'Table'[User Email] = EARLIER('Table'[User Email])
                    )
                )=1,
                1,0
            )
        )
    return
        SUMX(
            newtab,
            [flag]
        )
)

return
SUMX(
    tab,
    [flag]
)

CountNotCompleted = 
var _training = SELECTEDVALUE('Table'[Training Title])
var tab = 
SUMMARIZE(
    'Table',
    'Table'[Training Title],
    "flag",
    var newtab =
        ADDCOLUMNS(
            DISTINCT('Table'[User Email]),
            "flag",
            IF(
                CALCULATE(
                    MAX('Table'[% Complete]),
                    FILTER(
                        ALL('Table'),
                        'Table'[Training Title] = EARLIER('Table'[Training Title])&&
                        'Table'[User Email] = EARLIER('Table'[User Email])
                    )
                )<1,
                1,0
            )
        )
    return
        SUMX(
            newtab,
            [flag]
        )
)

return
SUMX(
    tab,
    [flag]
)

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 one of the two

calculate(count(Table[User Email]), filter(Table,[Title % Complete]<100))

or
Sumx(summarize(Table,Table[User Email],Table[Training], "_1",calculate(count(Table[User Email]), filter(Table,[Title % Complete]<100))),[_1])

[Anonymous]

Hi @amitchandak 

Thanks for your reply. 

Both DAX won't work cause they are not excluding the rows where I have one user who took one training and complete for example 30% first time but 100% second time.

Just to give you example:
For Training 1: User 1 first watched 30% of the training, but later he watched 100% of the training. 

So when doing the count for non completed training, he needs to be excluded.

With you DAX, he will be included.

The logic that should be applied by DAX is to count rows where % Complete is less than 100% and ignore any rows If the a user has another row where % complete is 100% in addition to value less than 100%.

[Anonymous]

Thanks @Alan 

I definetly wouldn't have come to such solution by myself, specially that I'm starting to use Power BI recently 🙂