Optimizing PERCENTILEX

Will think on it, but I doubt it.  Nice job on sharing it in a clever way that is easily scaleable to test out potential solutions.  This really highlights the need for visual-level and/or page-level variables.  Not sure if that idea is already suggested or not, but you should if not.

Regards,

Pat

Hey AIB,

I will look into your question in the upcoming days off, so it can take a while 🙂

Regards,

Tom

AlB  Glad it helped.  I love a good DAX or M challenge, and this was a fun one.  Most times we don't need to use the value term of RANKX, but it comes in handy.  Thank you for sharing your expression to see the improvements you've made.  If we could make custom functions like in M, this would be a good one to keep handy. 

It does also highlight the potential of having visual level measures that could be referenced on each row, which would make a much bigger improvement still.  I added that idea here.

Regards,

Pat

AlB 

Interesting thoughts. You created a [Value] column based on the PercentileM measure, but I guess you just cannot avoid the measure calculation once you add the client code in the visual.

Regards
Paul

mahoneypat  TomMartens  (and everyone else)

Pat, thanks for creating the idea for the visual-level variables. 

I was having a look further on question 2.  Imagine we have a simple table visual with Table1[Client_code] and this measure that  uses the built-in PERCENTILEX.INC:

PercentileM_INC with NumberCol = 
VAR wantedPerc_ = [Wanted_perc]
RETURN
PERCENTILEX.INC(ALL(Table1[Client_code]), CALCULATE(SUM(Table1[number])), wantedPerc_)    

where [Wanted_perc] is simply a constant. We can then get the query for this visual from the Performance Analyzer:

// DAX Query
DEFINE
  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL('Table1'[Client_code], "IsGrandTotalRowTotal"),
      "PercentileM_INC_with_NumberCol", 'Table1'[PercentileM_INC with NumberCol]
    )

  VAR __DS0PrimaryWindowed = 
    TOPN(502, __DS0Core, [IsGrandTotalRowTotal], 0, 'Table1'[Client_code], 1)

EVALUATE
  __DS0PrimaryWindowed

ORDER BY
  [IsGrandTotalRowTotal] DESC, 'Table1'[Client_code]

 If we run this in DAX Studio and N (number of clients) is 1M, it takes over an hour and crashes, as stated earlier. We can however modify the query to apply that visual-level variable: 

// DAX Query
DEFINE
    VAR valForAllRows_ = 'Table1'[PercentileM_INC with NumberCol] //Executed only ONCE for the visual 

    VAR __DS0Core_V2 = //This is the more efficient variation
        SUMMARIZECOLUMNS (
            ROLLUPADDISSUBTOTAL ( 'Table1'[Client_code], "IsGrandTotalRowTotal" ),
            "PercentileM_INC_with_NumberCol", valForAllRows_ //Using the variable  here
        )

    VAR __DS0PrimaryWindowed =
        TOPN (
            502,
            __DS0Core_V2, //CHANGE here to swap between versions
            [IsGrandTotalRowTotal], 0,
            'Table1'[Client_code], 1
        )
EVALUATE
__DS0PrimaryWindowed
ORDER BY
    [IsGrandTotalRowTotal] DESC,
    'Table1'[Client_code]

the above executes in 2 secs

I was wondering whether something like this can be implemented in a custom visual, where you can determine how the visual is run internally?? Perhaps with Charticulator or with a Python visual? 

Thoughts?

Thanks

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

I'm sure you could do the same in R or Python and it would also be fast.  I don't think Charticulator would apply here, but it is an interesting idea to be able to edit the MDX behind a table/matrix visual directly.  You should propose it.

Regards,

Pat

Regards,

Pat

TomMartens 

Thanks a lot for your reply. This looks GREAT, very interesting. I just saw that the results your measure produces are not exactly the same as what PERCENTILEX.INC yields. I believe however that is simply the interpolation and probably will not take much to fix. A few questions. I know you are on holidays. No rush 😊

1. I haven't worked with PATH objects. I will do my own research but would you care to explain, high level, the logic behind the approach?

2.  How do you build the dedicated Client_code table? Is it just an aggregation, with one row per client and their total revenue?

3.  "the measure will benefit from filterpropagation instead of permanently scanning the table"  What do you exactly mean by this? 

4. "In regards to your 2nd question, maybe there is the possibility to create a table that just contains a single row using GROUPBY or SUMMARIZE. Then this table will calculated only once during data refresh" Is this different from what I proposed initially in question 2, with the MeasureResult table and [ReadFromTable]? If not, we would still have the problem of it being a static solution, not responding to filters

5.  "You can use a more simple measure to blend the PERCENTILE value into your fact table" Can you elaborate?

Many thanks and a happy Christmas to you too

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers