Forum Discussion

smpa01's avatar
smpa01
Community Champion
5 months ago
Solved

Rank vs Rankx

AlexisOlson  I’m experimenting with the RANK window function vs. the traditional RANKX pattern. While I understand that RANK is now the recommended approach by SQLBI for performance and clarity,...
Need Help
  • FBergamaschi's avatar
    FBergamaschi
    5 months ago

    Hi smpa01,
    I created the RANK version as follows

     

    dax_RANK =
    RANK (
        DENSE,
        ADDCOLUMNS(
        ALLSELECTED( 'fact' ),
        "@period", RELATED( dim_period[period] ),
        "@Mycol", [fact_sum]
        ),
        ORDERBY ( [@Mycol], DESC ),
        PARTITIONBY ( [@period] )
    )
     
    It works in my pbix both with a without the name in the visual
     

     

     

     

    If this helped, please consider giving kudos and mark as a solution

    @me in replies or I'll lose your thread

    Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

    Consider voting this Power BI idea

    Francesco Bergamaschi

    MBA, M.Eng, M.Econ, Professor of BI

     
     
smpa01's avatar
smpa01
Community Champion
5 months ago

Thanks amitchandak . But problem remians the same. RANKX lets me get away with minimum maintenance with fact and partition. Byt RANK needs me to build a Relations table for the Relations parameter that needs all the rows that are present in the visual. When you add or remove rows to the visual, you need to go back to the measure and manually add them, which is not what I want. 

 

When I pass fact table to RANKX it doesn't care about the columns in your visual. It is a preferred pattern for me cause I don't want to keep track what visual gets added or removed.

Can RANK provide exact same facility ?

FBergamaschi's avatar
FBergamaschi
Super User
5 months ago

Hi smpa01,
I created the RANK version as follows

 

dax_RANK =
RANK (
    DENSE,
    ADDCOLUMNS(
    ALLSELECTED( 'fact' ),
    "@period", RELATED( dim_period[period] ),
    "@Mycol", [fact_sum]
    ),
    ORDERBY ( [@Mycol], DESC ),
    PARTITIONBY ( [@period] )
)
 
It works in my pbix both with a without the name in the visual
 

 

 

 

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI