How can I make my dax calculation run faster and in the proper execution proper order?

[mlane]

Attached in the links below is my workbook and some anonymized sample data.

1. MockData.csv 
2. 90p_anonymous_data.pbix 

So I figured out how to calculate a very complicated nonparametric percentile calculation my organization uses.  These are the challenges I have experienced so far.  For example, whenever I edit the calculation it takes about 5 minutes for it to load still and i am not sure why.   It was tricky because I have to make sure it removes duplicates and trims the data before it calculates the 90th percentile.   It was also tricky because I cannot do it all in one single measure/calculation as for some reason as it does not register the other variables when I have a dax calculation.  

Is there a better or faster way to do this 90th percentile calculation?  

Is there a way I can specify which DAX queries run first in a DAX calculation?  Or can I make a virtual table to stage the calculation? 

[darkniqht]

Instead of doing everything in one measure, you can break it down into smaller measures. For example, create a measure to remove duplicates, another to trim the data, and finally one for calculating the percentile. This can make it easier for Power BI to process and might speed things up.

Yes, you can create virtual tables in DAX using VAR to store intermediate results. This allows you to organize your calculations step-by-step and might improve performance. Here’s a simple example:

VAR CleanedData = 
    DISTINCT(YourTable[YourColumn]) // Remove duplicates

VAR TrimmedData = 
    ADDCOLUMNS(CleanedData, "TrimmedValue", TRIM(YourTable[YourColumn]))

RETURN
    PERCENTILEX.INC(TrimmedData, [TrimmedValue], 0.9)

In DAX, the order of evaluation can be tricky. Using variables as shown above can help you control the sequence of operations. However, remember that DAX is optimized for context, so ensure you understand the row context and filter context when writing your calculations.

Besides breaking down your calculations, check for other potential performance issues, such as data model size and relationships. Reducing the data volume or complexity can also help.

[lbendlin]

Use DAX Studio.  Examine the Query Plan. Refactor your query to reduce the record counts.

Your sample data seems to perform fast enough.  Where exactly do you see the delay?

[mlane]

Soo.... it turns out external tools has to be approved by my organization before use.  So, I will reply back once I have a working copy.
Is Darren Gosbell of https://daxstudio.org/blog/ the same person as the Microsoft Employee Darren Gosbell?  It would help in the approval process if this was true.