Ranking occurrences of strings on multiple columns

[Anonymous]

Hello everyone, 

I have trouble trying to find the 3 more frequent strings appearing in different columns of a table;

for example if I have 

col1	col2	col3
X	B	Y
A	Y	Z
Z	X	Z
X	Y	X
Y		X
	B	C

 What I want is to get (in a table report for example) the list of every String sorted like this :

String	Number of occurrences	Ranking of occurrences
X	5	1
Y	4	2
Z	3	3

In the example we have only letters but it can be anything like texts, names, etc., not known in advance. The real table obviously has many more columns, and filtering with different criterias.

It is very tricky because I have to "merge" 3 different rankings in one, with different values a same row.

I've searched in this forum without finding any solution, so thank you if you have a clue.

[CNENFRNL]

Hi, @Anonymous , you might want to in the first place unpivot the original dataset in a simple one-dimensional table; and all gets easy.

Occurrence = COUNTROWS( Table1 )

Rank by occurrence = RANKX( ALLSELECTED( Table1[String] ), [Occurrence] )

[polymathy2017]

Re: your byline: the same could be said about the ability to write coherently...beyond the comprehension of many (or most!). 

[Greg_Deckler]

@Anonymous Seems like you want Mode for multiple columns? Mode - Multiple Columns Greg_Deckler  0 (powerbi.com)

You might also consider unpivoting those columns in Power Query.