Forum Discussion

kleigh's avatar
kleigh
Responsive Resident
3 months ago
Solved

Slow visual calculation (PREVIOUS and RUNNINGSUM)

Background:

I'm trying to add a visual indication of groups (each person can have multiple rows) to a table.

I have made a visual calculation that performs this in three parts. 

Part one detects rows where there is a change of group:

DC = if(PREVIOUS([Person Key], 1, ROWS) = [Person Key], 0, 1)

 
Part two turns this into a group number

GN = RUNNINGSUM([DC])

 

Part three turns the group number into a colour using MOD of the group number. I'm leaving it out of the scope here as I get the performance problems without it and how it works is a whole other distraction.

The result is fine for tables with low numbers of rows or simple queries. Higher row counts or more complex queries can make it take a long time to complete or give a resource exceeded error. This can be in the range of a few thousand rows. It's hard to pinpoint the exact amount as sometimes the resource error only appears once the table is scrolled. Typically, the table would be filtered and reduce the row count, but I don't want to show the users a broken visual if they haven't applied filters yet - and it's possible some filter combinations will hit the limit anyway.

It's definitely the visual calculation as I can run a page with two identical tables except only one has the VC. The table without the calculations will load instantly and the table with them will lag severely. I used this method as it should be robust if the user sorts the table and never display false groups. I've tried various things to fix the calculation. Adding the MOD to the group number VC doesn't help. A calculation asking for its own previous value is counted as a circular error, so I can't skip RUNNINGSUM. RUNNINGSUM won't accept an expression so the change part has to be separate.

Is there another way to do this that performs better?

  • You need to avoid a RUNNINGSUM as that is an inherently expensive operation. If what you are trying to achieve is to visually highlight when rows belong to the same or different people, I think I would assign each person a colour, so that no matter what the order of the visual is the same person always has the same colour.

    I think you could do this by using the RANK method I proposed and applying the MOD logic within the measure, or using some other way to turn a unique Person Key into a unique colour.

5 Replies

  • Your running sum is effectively just returning a ranking, so you could try creating a measure which returns the ranking instead. Assuming that Person Key is unique, you could create a measure like

    Person Key Rank =
    RANK ( ALLSELECTED ( 'Person'[Person Key] ) )
    

    You can then either add that to the visual and create a visual calc to perform your MOD arithmetic, or amend the measure to include the MOD in there as well.

    • kleigh's avatar
      kleigh
      Responsive Resident

      While that would work with the default sort, I'm trying to make it also work if it's sorted.

      With another sort, sometimes the rank+mod will coincide on the same value.

      I'm not expecting it to always be useful in this situation, but at least avoid being misleading.

      • johnt75's avatar
        johnt75
        Super User

        You need to avoid a RUNNINGSUM as that is an inherently expensive operation. If what you are trying to achieve is to visually highlight when rows belong to the same or different people, I think I would assign each person a colour, so that no matter what the order of the visual is the same person always has the same colour.

        I think you could do this by using the RANK method I proposed and applying the MOD logic within the measure, or using some other way to turn a unique Person Key into a unique colour.

  • v-prasare's avatar
    v-prasare
    Community Support

    Hi kleigh ,

    We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

     

     

    Thank you for your patience and look forward to hearing from you.
    Best Regards,
    Prashanth Are
    MS Fabric community support

  • v-prasare's avatar
    v-prasare
    Community Support

    Hi @kleigh ,

    We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

     

     

    Thank you for your patience and look forward to hearing from you.
    Best Regards,
    Prashanth Are
    MS Fabric community support