Forum Discussion

m_roussakis's avatar
m_roussakis
Icon for Helper III rankHelper III
4 years ago
Solved

DAX help to count 100% completion

I have a matrix table which shows the training completion rate per region and employee.  I have about 8000 employees, each assigned multiple courses to complete.    As you can see from the scree...
Need Help
m_roussakis's avatar
m_roussakis
Icon for Helper III rankHelper III
4 years ago

I need to make an edit to the gtreat DAX you gave me.

How can I EXCLUDE specific rows from this calculation. So I want to exclude some Course Titles:

 

Column A = User ID

Column B = Course Title (EXCEPT where title = "ABC123")

Column C = Course Completion status

Column D = Location

 

Thanks

m_roussakis's avatar
m_roussakis
Icon for Helper III rankHelper III
4 years ago

I tried to do it myself and it's not working:

 

Here's my attempt. My addition is shown underlined. 

 

User Completion =
VAR tbl=SUMMARIZE('Training','Training'[COUNTRY],Training[User - User ID],"completed",DIVIDE(CALCULATE(COUNTROWS('Training'),FILTER('Training','Training'[Transcript - Transcript Status]="Completed" || 'Training'[Training - Training Title]<>"MRS - Module 18: Additional Features - August 20220" )),CALCULATE(COUNTROWS('Training'),ALLEXCEPT('Training','Training'[User - User ID]))))
return COUNTROWS(FILTER(tbl,[completed]=1)) +0
  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    4 years ago

    m_roussakis 

    maybe you can try this

    Measure =
    VAR tbl=SUMMARIZE('Table','Table'[Location],'Table'[User ID],"complete",DIVIDE(CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Course Completion status]="Y" && 'Table'[Course Title]<>"ABC123")),CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[User ID]),'Table'[Course Title]<>"ABC123")))
    return COUNTROWS(FILTER(tbl,[complete]=1))
     
    pls see the attachment below