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
    )

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi  CalexUK ,

 

Here are the steps you can follow：

1. Create calculated table.

Table 2 =
var _summarize =
SUMMARIZE(
    'Table',
    [User ID],
    "training programmes","completed %")
var _table=
SUMMARIZE(
    'Table','Table'[User ID],'Table'[training programmes])
return
UNION(
    _table,_summarize)

2. Create measure.

Flag_1 =
MAXX(
    FILTER(ALL('Table'),
    'Table'[User ID]=MAX('Table 2'[User ID])&&'Table'[training programmes]=MAX('Table 2'[training programmes])),[Measure])
Flag_2 =
IF(
    MAX('Table 2'[training programmes]) ="completed %",
    DIVIDE(
    COUNTX(
        FILTER(ALLSELECTED('Table 2'),'Table 2'[User ID]=MAX('Table 2'[User ID])&&
    [Flag_1]="Completed"),[User ID]),
    COUNTX(
        FILTER(ALLSELECTED('Table 2'),'Table 2'[User ID]=MAX('Table 2'[User ID])),[User ID])-1),
    [Flag_1])

3. Result:

 

Best Regards,

Liu Yang

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