topic Re: Counting Text Values Across Multiple Dynamic Columns by User in DAX Commands and Tips

Counting Text Values Across Multiple Dynamic Columns by User

CalexUK — Sat, 04 Mar 2023 09:55:54 GMT

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 (User dp0m6gp) has completed 4 of these so I want to show this as a percentage and so on for each user ?

I also have a slicer that shows different training programmes that have more or less training outcomes therfore the number of learning outcomes increases or decreases for each programme.

Re: Counting Text Values Across Multiple Dynamic Columns by User

tamerj1 — Sat, 04 Mar 2023 10:13:46 GMT

Hi CalexUK

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

Re: Counting Text Values Across Multiple Dynamic Columns by User

Anonymous — Thu, 09 Mar 2023 01:23:32 GMT

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

Re: Counting Text Values Across Multiple Dynamic Columns by User

CalexUK — Thu, 09 Mar 2023 05:41:36 GMT

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

Re: Counting Text Values Across Multiple Dynamic Columns by User

tamerj1 — Thu, 09 Mar 2023 07:15:09 GMT

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 )

Re: Counting Text Values Across Multiple Dynamic Columns by User

CalexUK — Sat, 11 Mar 2023 18:41:57 GMT

Thank you this worked perfect...

Re: Counting Text Values Across Multiple Dynamic Columns by User

CalexUK — Mon, 13 Mar 2023 12:05:06 GMT

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

Re: Counting Text Values Across Multiple Dynamic Columns by User

tamerj1 — Tue, 14 Mar 2023 05:44:11 GMT

CalexUK
Perhaps somthing like "< Parameter" makes sense?