Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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
[email protected]Training 130%
[email protected]Training 1100%
[email protected]Training 130%
[email protected]Training 1100%
[email protected]Training 160%
[email protected]Training 1100%
[email protected]Training 2100%
[email protected]Training 230%
[email protected]Training 220%
[email protected]Training 275%

 

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

    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.

4 Replies

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

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

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

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

        Thanks Alan 

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