cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Counting Rows using condition for more than one column

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 EmailTraining Title% Complete
user1@company.comTraining 130%
user1@company.comTraining 1100%
user2@company.comTraining 130%
user3@company.comTraining 1100%
user3@company.comTraining 160%
user4@company.comTraining 1100%
user1@company.comTraining 2100%
user2@company.comTraining 230%
user3@company.comTraining 220%
user4@company.comTraining 275%

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Hi, @YJAMOUS 

 

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:

a2.png

 

Best Regards

Allan

 

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

View solution in original post

4 REPLIES 4
Super User IV
Super User IV

@YJAMOUS , 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])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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%.

Community Support
Community Support

Hi, @YJAMOUS 

 

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:

a2.png

 

Best Regards

Allan

 

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

View solution in original post

Thanks @Alan 

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

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors