RANKX timing out due to memory

[paguy215]

I have a large table of records of agents and sales, wich each row representing one sale.   I want the table visual to show a ranking of the total sales amount (sum of all rows) for each agent in a time frame the user selects a slicer

This the formula I have

Revenue Rank =

RANKX(ALLSELECTED('Agent List'[AgentID] ),CALCULATE(sum('Sales'[AMOUNT])))

The Agent List table is a seperate table linked to the Sales table, basicallly just a list of every unique name and ID...  but I'm getting the same issue with that as I do if I just use the name in the sales table, which is that adding the formula to the table takes forever and eventually times out due to memory.   

Is there a better way to do this?  I get its taking long becuase its reading each row, but I can't get it to work

[Anonymous]

Hi  @paguy215 ,

You can try the following dax, if you use ALLSELECTED('Agent List'[AgentID]), it is traversing the rank for each row of AgentID, and if you use ALLSELECTED('Agent List'), it is traversing the rank for the table Agent List:

Revenue Rank =
RANKX( ALLSELECTED('Agent List'), calculate(sum('Query1'[SalesSum])))

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[paguy215]

Thanks, still doesn't work though.  I get some values other than 1 but still dozens or rows with different SalesSum amounts all ranked as 1

[Poojara_D12]

Hi @paguy215 

To optimize your Revenue Rank calculation, you can first aggregate the sales by agent using the SUMMARIZE function and then rank the agents based on total sales. This reduces the row context and improves performance. Here's the optimized DAX formula:

Revenue Rank = 
VAR AgentSales = 
    SUMMARIZE(
        'Sales', 
        'Agent List'[AgentID], 
        "TotalSales", SUM('Sales'[AMOUNT])
    )
RETURN
    RANKX(
        AgentSales, 
        [TotalSales], 
        , 
        DESC, 
        Dense
    )

This approach calculates total sales per agent before ranking, reducing the workload and improving performance. It avoids row-by-row calculations, making it much more efficient.

 

[paguy215]

Thanks but same error, it seems to not want to recognize the created column name

[Anonymous]

Thanks for the reply from Sahir_Maharaj , please allow me to add some more information:

Hi  paguy215 ,

Does your Agent List table have a lot of duplicate [AgentID]?

You can try creating a new table that contains only distinct [AgentID], then link the new table to the customer table and replace the Agent List table in the formula with the new table.

new table =
DISTINCT ( 'Agent List'[AgentID] )

Revenue Rank =
RANKX (
    ALLSELECTED ( 'new table'[AgentID] ),
    CALCULATE ( SUM ( 'Sales'[AMOUNT] ) )
)

If you need the rankx function to perform a dynamic display of TopN, you can use the TOPN() function to omit the steps:

TOPN function (DAX) - DAX | Microsoft Learn

The slow loading of Power BI Desktop is related to other factors apart from functions and you can check the following:

You can use DAX Studio to optimize the inspection of DAX statements from a performance perspective and use the Performance analyzer to check the performance of each of your report elements when users interact with them, and which aspects of performance consume the most (or least) resources

DAX Studio | DAX Studio

Use Performance Analyzer to examine report element performance in Power BI Desktop - Power BI | Micr...

Use a star schema design to simplify relationships and improve query performance:

DAX Optimization Tips for Large Models - Microsoft Fabric Community

You can optimize the data model and increase the default values of the corresponding options to optimize query performance and minimize the number of visual objects on the report, each of which increases load time.

Optimization guide for Power BI - Power BI | Microsoft Learn

Evaluation configuration settings for Desktop - Power BI | Microsoft Learn

 There are also some suggestions for optimizing the model here:

• Remove unused tables or columns, where possible. 
• Avoid distinct counts on fields with high cardinality – that is, millions of distinct values.  
• Take steps to avoid fields with unnecessary precision and high cardinality. For example, you could split highly unique datetime values into separate columns – for example, month, year, date, and so on. Or, where possible, use rounding on high-precision fields to lower cardinality – (for example, 13.29889 -> 13.3).
• When connecting to data sources via Direct Query, consider indexing columns that are commonly filtered or sliced again. Indexing greatly improves report responsiveness. 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[paguy215]

Spoiler

Hello,

so i ended up creating a new query to aggregate things there, and I can get the RANKX on the visual fairly easily...however now everything is ranked as 1

here is what i have

Revenue Rank =

RANKX( ALLSELECTED('Agent List'[AgentID]), calculate(sum(Query1[SalesSum])))

SalesSum is calculated column on the new query, taking the sum of 1 column (tracked sales) and a 2nd column (non-tracked sales)... I tried removing the calculate part but it doesn't change anything

[paguy215]

Thanks, yeah its in a seperate table with distinct IDs.  I'll try and look at some of the other things you mentioned

[Sahir_Maharaj]

Hello @paguy215,

Can you please try this approach:

Revenue Rank =
RANKX(
    ADDCOLUMNS(
        VALUES('Agent List'[AgentID]),
        "TotalSales", CALCULATE(SUM('Sales'[AMOUNT]))
    ),
    [TotalSales],
    ,
    DESC,
    Skip
)

[paguy215]

Hello,

What is the "TotalSales", i'm getting a column doesn't exist error

[Sahir_Maharaj]

Thanks for your response @paguy215.

Can you please try the following:

Revenue Rank = 
VAR TotalSales = 
    SUMMARIZE(
        'Sales',
        'Sales'[AgentID],
        "TotalAmount", SUM('Sales'[AMOUNT])
    )
RETURN
    RANKX(
        TotalSales,
        [TotalAmount],
        ,
        DESC,
        DENSE
    )

[paguy215]

Actually i think i see the issue.  I also have a region and store column in the visual...when I remove those and just have the agentID and name, it works.   So how can I get this to work with those two columns added?   I've tried it with the store info in the same table and a unique table

[paguy215]

Thanks, but I am still getting the same error ... 'either column doesn't exist or there is no row for this column'