Calculated Column to determine most frequent text in "range"

[Endre]

Hi,

I have a huge data table where I would like to categorise each row in a calculated column so I can use it as filter in Powerpivot table later on.

My problem is that there are different columns and different methods to allocate a row to a specific category and they may not necessarily have the same result. 

I have now 4 methods in 4 calculated columns and I would like to use the most frequent (non-blank) one as category.

E.g.:

[M1]      [M2]      [M3]      [M4]      [Category]

AAA3    AAA3     AAA3    AAA3     AAA3       (clear case, all methods get the same result)

BBB3    CCC2      blank    CCC3      CCC3       (different results but "CCC3" appeared most)

blank    blank     blank    DDD2     DDD2       (only one method resulted in a positive match)

Is it possible at all?

It could be achieved in the linked table (worksheet) but it would boost the size of the file and calculation would slow it down at the same time. By the way none of the required functions (MODE, INDEX, MATCH) are available in DAX.

Thank you for any suggestion,

Endre

[Greg_Deckler]

@Endre- OK, I got this far but got sucked into some other stuff. But I figured I'd pass it along because it's close. I was trying to come up with the table you would need based upon the information you provided. So I created this Enter Data table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnR0NFbSwUbF6kQrOTk5gfjOzs4gCsYCyUC4QOTi4mKkFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [M1 = _t, M2 = _t, M3 = _t, M4 = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"M1", type text}, {"M2", type text}, {"M3", type text}, {"M4", type text}})
in
    #"Changed Type"

and then this Table definition (that would eventually go into a measure or calculated column:

Table = 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]))
RETURN myTable1

[Greg_Deckler · Accepted solution]

Oooo! I think I got it @Endre!

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