Ranking when multiple numeric values repeats in a column

[LauraE]

Hello all, 

I am relatively new to Power BI and are struggling to find the correct DAX code (or solution in general) to my ranking problem. To set some background, I am working with a huge dataset (questionnaire form), which in the first try was formatted wide with app. 200 columns (and then five of those datasets) - each column as a new variable. However, loading problems caught up with med and I decided to make my datamodel long with four columns. 1) ID-number, 2) variable name, 3) numeric answer to the question/variable, 4) label name. 

DAX-functions then come in handy, because I need to create measures for all of my former variables. However, I have problems figuring out how to make the ranking work correctly according to the natural ranking of the variable. Ex. education would be ranked as 1) No education, 2) Primary education, 3) Secondary, 4) Bachelors-degree, etc. and not according to either alphabetical order or the frequency of the answer. 
I have tried to make a unique ranking of all the possible answers (1-3000+) in PQ in the label dataset, but when I merge it with the primary dataset the ranking number will appear multiple times (because some people have answered the same), thus disabling me to sort by the ranking column. And putting in the ranking column in tooltip, just makes the data in the frequency table disappear, as other answers have a smaller/larger number. 

My DAX-code so far (without the ranking) looks like this: 

Educational_level =

CALCULATE(

    COUNT('Table 1'[ID-number]),

    'Table 1'[Variable name]= "o3_education"

)

Is there any way to secure the ranking in my original DAX-code? Making additional tables or columns for each of the 100 rankings would be a lot of work and probably not work in the long run, load wise. 

I have created an example of how my data is structured: 

[LauraE]

Hi @Anonymous,

In essence it did work - however, it still won't let me sort by 'Rank' in the tabel viewing... I think I misunderstood the ranking issue the program informed me about. The problem is probably more the fact that my label names repeats itself in multiple variables, fx the option 'male' is both a possible answer in variable 1, 5 and 7. Thus, the ranking number for the option 'male' needs to be the same to solve the 'sort by' issue in tabel viewing. But creating a ranking based on the label name randomise the natural order of fx education. Then the answer 'no education' has rank 136, 'bachelors degree' has rank 112 and 'primary education' rank 188. So, I need to create a column which ranks according to the natural order within the questions (defined by the numbers of the 'answer'-column), but repeats a rank number if the label name has already been given a number before, and at the same time each unique label name needs to have a unique rank number.

Is it something you by any chance also know the answer to? 

Best regards,
Laura

[Anonymous]

Hi @LauraE ,

I have a workaround, you can try using rankx sorting and with the help of ID, which avoids duplicate values:

Rank =
RANKX(
    ALL('Table'),
    'Table'[Your sort measure] + (0.1 / 'Table'[ID]),
    ,
    DESC
)

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.