Mode - Multiple Columns 02-15-2018 12:24 PM - last edited 06-27-2018 13:41 PM

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490... you have a table of data with multiple columns like this...

M1         M2          M3        M4         Index

AAA3	AAA3	AAA3	AAA3	0
BBB3	CCC3		CCC3	1
			DDD2	2

...and you wish to figure out what value occurs most often in those columns. You can use a measure like this:

Mode = VAR myTable = UNION(UNION(UNION(SUMMARIZE(Mode,Mode[Index],"Methods",MAX(Mode[M1])),SUMMARIZE(Mode,Mode[Index],"Methods",MAX(Mode[M2]))),SUMMARIZE(Mode,Mode[Index],"Methods",MAX(Mode[M3]))),SUMMARIZE(Mode,Mode[Index],"Methods",MAX(Mode[M4])))
VAR myTable1 = FILTER(GROUPBY(myTAble,[Index],[Methods],"Count",COUNTX(CURRENTGROUP(),[Methods])),[Methods]<>"")
VAR myTable2 = FILTER(myTable1,[Count]=MAXX(myTable1,[Count]))
VAR Mode1 = MAXX(LASTNONBLANK(myTable2,[Methods]),[Methods])
RETURN Mode1

This quick measure would take as input a unique identification column, and the columns in which to calculate the mode.

eyJrIjoiNWI5OTJkMWUtMWFkNS00MjkxLWFiZGMtN2Q1ZmRkMTQ0NzI1IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9