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

[YGY]

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.

[LeventK]

@ 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"

[YGY]

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

[Steve]

YGY:

a filtered view based on the result

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

YGY:

another table with a description of the 5 categories

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.

[YGY]

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