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

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 🙂