powerbidaxpowerbi-desktopwindow-functionsranking-functions

Rank window function not working -showing only ones


The below Rank Window function doesn't work. Rank only gives me ones. I am trying to rank the measure "% Change Quarter" against 'Calendar'[Year] and 'Calendar'[Quarter]:

EVALUATE
    SUMMARIZECOLUMNS(
        'Calendar'[Year],
        'Calendar'[Quarter],
    "% Change Quarter", [% Change Quarter],
    "RNK", rank(
    DENSE,
        SUMMARIZECOLUMNS(
            'Calendar'[Year],
            'Calendar'[Quarter],
        "% Change Quarter", [% Change Quarter]
        ),
    ORDERBY([% Change Quarter], DESC),
    PARTITIONBY('Calendar'[Year])
        )
    )
    

Output Below:

|Calendar[Year]|Calendar[Quarter]| [% Change Quarter]|[RNK]
-----------------------------------------------------------
|2023          | Q2              |0.07               |1
|2023          | Q3              |-0.11              |1
|2023          | Q4              |0.57               |1
|2024          | Q1              |0.69               |1
|2024          | Q2              |-0.12              |1
|2024          | Q3              |0.01               |1

What am I missing, is there a problem with my filter context for year and Quarter? Does it need an All filter function, because I tried that with summarizecolumn but it is not working.

The below is the PBIX file: https://1drv.ms/u/c/1e7368b2a4fe3faa/EdliZDH7XtlItZYYIRZasBABiGHzsp-whGV-q1mqOW0KiQ?e=9bnnii


Solution

  • Does this do what you want?

    EVALUATE
    
        SUMMARIZECOLUMNS(
            'Calendar'[Year],
            'Calendar'[Quarter],
            "% Change Quarter", [% Change Quarter],
            "RNK", RANK(
                DENSE,
                CALCULATETABLE(SUMMARIZECOLUMNS(
                    'Calendar'[Year],
                    'Calendar'[Quarter],
                    "% Change Quarter", [% Change Quarter]
                ), REMOVEFILTERS('Calendar'[Year],'Calendar'[Quarter])),
                ORDERBY(
                    [% Change Quarter],
                    DESC
                ),
                PARTITIONBY('Calendar'[Year])
            )
        )