Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Raking column versus ranking Measure

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 :

The_Guibu_0-1646236671261.png

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

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Hello @Greg_Deckler,

 

Thank you very much for that link.

Unfortunately, not working better :

The_Guibu_0-1646304883981.png

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

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.