Raking column versus ranking Measure

[Anonymous]

Hello All,

I am trying to solve a terrific trouble.

I have a ranking formula which is working perfectly fine for a measure but which is totally wrong for a column.

"Ranking" 3 and "Ranking 3 Measure" use the same formula but one is displaying 38 and the other one the right ranking :

This is the formula :

Ranking 3 = 
MINX(
    FILTER(
        SELECTCOLUMNS(
            ALLSELECTED('Tesson''s Curve'),
                "index",'Tesson''s Curve'[Index],
                "rank",RANKX(ALLSELECTED('Tesson''s Curve'),'Tesson''s Curve'[NM%],,DESC,Dense)
            ),
            [index]=MAX('Tesson''s Curve'[Index])
                ),
            [rank]
            )

The trick is that i need to have the ranking to be updated when filtering on the "Orga".

Is there any way I can get this to work ?

Thanks in advance for your help.

Guillaume

[Anonymous · Accepted solution]

hello @v-shex-msft, 

Thanks for your reply.

Unfortunately, since PowerBI is not able to support the dynamic ranking column, i have to find a way around to get what I want?

My real goal is in fact to create a dyanmic cumulative column/measure, based on a dynamic ranking.

Not suceeding so far unfortunately.

Anyway, I will close that topic now.

Thanks for your help to all of you, and have a nice day.

Guillaume

[Greg_Deckler]

@Anonymous See if this helps, https://community.powerbi.com/t5/Quick-Measures-Gallery/To-Bleep-with-RANKX/m-p/1042520#M452

[Anonymous]

Hello @Greg_Deckler,

Thank you very much for that link.

Unfortunately, not working better :

when I have a n Orga selected, I would like the column to display the same rank as in the "Ranking 3 measure".
Unfortunately, it's not the case, it's acting just like if i had added an index with M Query.

My concern is that i need the ranking to be dynamic as column, not as a measure.

By any chance, do you have any other idea ?

Kind regards,

Guillaume

[v-shex-msft]

Hi @Anonymous,

I think you can try to use add a filter to your rank formula to rank based on the current category.

Ranking =
VAR currGroup =
    VALUES ( 'Table'[Orga] )
RETURN
    MINX (
        FILTER (
            SELECTCOLUMNS (
                ALLSELECTED ( 'Tesson''s Curve' ),
                "index", 'Tesson''s Curve'[Index],
                "rank",
                    RANKX (
                        FILTER ( ALLSELECTED ( 'Tesson''s Curve' ), [Orga] IN currGroup ),
                        'Tesson''s Curve'[NM%],
                        ,
                        DESC,
                        DENSE
                    )
            ),
            [index] = MAX ( 'Tesson''s Curve'[Index] )
        ),
        [rank]
    )

>>My concern is that i need the ranking to be dynamic as column, not as a measure.

Current power bi does not support creating dynamic calculated column/table based on filter effects, you can only use measure formulas to achieve these.

Notice: the data level of power bi.

Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)

Regards,

Xiaoxin Sheng