Ranking with different granularity

DAX DAX Calculations
1

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

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

Data Modell
Data Modell936×785 28.8 KB

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.

Correct rank
Correct rank1276×292 48.6 KB

Rank based on Surveys and Questions
Rank based on Surveys and Questions828×309 22.3 KB

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.

Rank is not working
Rank is not working1299×564 95.5 KB

Rank based on Surveys Questions and Answers
Rank based on Surveys Questions and Answers733×324 20.5 KB

Any help is greatly appreciated.

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

2

@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
3

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:

4

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