Ranking with different granularity

I hope I could get some help with creating a Rank measure.

I do have data about surveys, questions to them, and their answers.

I would like by selecting a survey to be able to show the questions related to their surveys (ranked as 1st question, 2nd question, etc.) and their answers.
I managed to create a rank for questions when both surveys and questions are selected.
When I add the column “Question Type”, which is part of table Dim Question, the measure still works. It is the same granularity as column “Question”. So not any problems here.


However, when I add column “Answer” the granularity gets higher, the rank breaks, and I could not figure out how to make the measure work.


Any help is greatly appreciated.

Here is also the .pbix file.Ranking with different granularity.pbix (2.7 MB)

@tyankata

You can try this:

Rank SQKey 2 =
IF (
    ISINSCOPE ( 'Dim Question'[Question Type] )
        && ISINSCOPE ( 'Dim Question'[Question] )
        && ISINSCOPE ( 'Fact Survey Answer'[Answer] ),
    RANKX (
        CALCULATETABLE (
            SUMMARIZE (
                'Fact Survey Answer',
                'Dim Question'[Question],
                'Dim Question'[Question Type],
                'Fact Survey Answer'[Answer]
            ),
            ALLSELECTED ( 'Fact Survey Answer' )
        ),
        [Sum SQKey],
        ,
        ASC,
        DENSE
    )
)

image

If you want to break ties then refer this:

1 Like

Hi @AntrikshSharma,
Thank you for this formula!
It solved the problem.
I have actually looked at one of your previous posts - Understanding RANKX with additional fields
however, could not figure out who to make it work. :slight_smile:

@tyankata I know that one can be confusing because all the columns had a 1:1/same Granularity.