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.
Hi, @ORDR , 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] )
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Re: your byline: the same could be said about the ability to write coherently...beyond the comprehension of many (or most!).
@ORDR 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.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!