Forum Discussion

CalexUK's avatar
CalexUK
Frequent Visitor
3 years ago
Solved

Counting Text Values Across Multiple Dynamic Columns by User

Hi I have a Matrix Table as below, I want to count the total number of learning activitys then work out the completion percentage, for example here there are 7 learning outcomes in total and line 1 ...
COUNT
DAX
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi CalexUK 
    Should it be something like this?

    Result = 
VAR CurrentStatus = VALUES ( 'Table'[Status] )
VAR PercentCompleted = 
    DIVIDE ( 
        COUNTROWS ( FILTER ( 'Table', 'Table'[Status] = "Completed" ) ),
        COUNTROWS ( 'Table' )
    )
RETURN
    IF ( 
        HASONEVALUE ( 'Table'[UserID] ) && HASONEVALUE ( 'Table'[Programmes] ),
        CurrentStatus,
        PercentCompleted + 0
    )

     

tamerj1's avatar
tamerj1
Community Champion
3 years ago

Hi CalexUK 

is everything is one source table? What is the dax measure code that is placed in the values of the matrix?

CalexUK's avatar
CalexUK
Frequent Visitor
3 years ago

Hi Tamerj1

The Data is in a single table, there is no DAX measure, we are only using the Matris Visual to display the training course values (Completed, Not Completed)

 

Kindest Regards

 

Paul

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi CalexUK 
    Should it be something like this?

    Result = 
VAR CurrentStatus = VALUES ( 'Table'[Status] )
VAR PercentCompleted = 
    DIVIDE ( 
        COUNTROWS ( FILTER ( 'Table', 'Table'[Status] = "Completed" ) ),
        COUNTROWS ( 'Table' )
    )
RETURN
    IF ( 
        HASONEVALUE ( 'Table'[UserID] ) && HASONEVALUE ( 'Table'[Programmes] ),
        CurrentStatus,
        PercentCompleted + 0
    )

     

    • CalexUK's avatar
      CalexUK
      Frequent Visitor
      3 years ago

      Thank you this worked perfect...

      • CalexUK's avatar
        CalexUK
        Frequent Visitor
        3 years ago

        Hi Tamerj!

         

        This solution worked great, is there a way to add a filter to this measure so that I can filter out whos is at say 100% for all items ? or 50% etc ?

         

        Thanks In Advance

         

        Paul