Find The Highest or Lowest Value In A Row and Return the Column Headings

Hi, hoping to get help figuring this out.

  • I have 5 categories, 5 columns for each category (A,B,C,D,E).
  • I have a form (from the same table as 5 category columns table) with 42 Y/N questions. Each question scores a 1 if answered Y for the category it belongs to. I have this part working well, but don’t have to go with this method if there’s a better option.

What I’m try to achieve: After a user answers all 42 questions, I would like to know which three categories scored the highest and I then want to take that result to a filtered view based on the result (another table with a description of the 5 categories).

Thanks.

@ YGY
Below expression will get you a list of TOP 3 items for each record of your table that you are looking for:

TOP(
	SORT(
		LIST(
			CONCATENATE(
				COUNT(
					SELECT(
						TableName[CategoryA],
						AND(
							[_RowNumber]=[_THISROW].[_RowNumber],
							[CategoryA]=TRUE
						)
					)
				)," - Category A"," , ",
				COUNT(
					SELECT(
						TableName[CategoryB],
						AND(
							[_RowNumber]=[_THISROW].[_RowNumber],
							[CategoryB]=TRUE
						)
					)
				)," - Category B"," , ",
				COUNT(
					SELECT(
						TableName[CategoryC],
						AND(
							[_RowNumber]=[_THISROW].[_RowNumber],
							[CategoryC]=TRUE
						)
					)
				)," - Category C"," , ",
				COUNT(
					SELECT(
						TableName[CategoryD],
						AND(
							[_RowNumber]=[_THISROW].[_RowNumber],
							[CategoryD]=TRUE
						)
					)
				)," - Category D"," , ",
				COUNT(
					SELECT(
						TableName[CategoryE],
						AND(
							[_RowNumber]=[_THISROW].[_RowNumber],
							[CategoryE]=TRUE
						)
					)
				)," - Category E"," , "
			)
		),TRUE
	),3
)

This expression will create you a list i.e.

"15 - Category A , 10 - Category C , 6 - Category E"
or
"28 - Category E , 12 - Category B , 2 - Category A"
4 Likes

In what way do yo want the view filtered? Only showing the top three? Showing all, but ordered by the user’s answer counts?

If you already have separate table with one row per category, you could add a virtual column to each row that counts the TRUE values in the corresponding question row columns.

2 Likes

Thanks @LeventK and sorry it took this long to get back to you, your expression helped a lot.

and @Steve - really appreciate your help, sorry for the delay in getting back to you.

1 Like