Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Advice for Filtering Large Data

I created a measure which counts the most recent status for a transaction. With that measure, I created another measure which will calculate the total $$$ value using the first measure as filter cont...
Need Help
Tutorial Requests
  • AlexisOlson's avatar
    AlexisOlson
    5 years ago

    You are essentially using Measure 1 as a calculated column in Measure 2. A more efficient way to do this would be to define a calculated column with 1 if the transaction is the latest and blank otherwise. Storing this as a calculated column, we only have to do it once and can reuse it in multiple measures.

     

    IsLastTransaction = 
IF (
    'All Patterns'[MIN_LAST_MODIFIED]
        = CALCULATE (
            MAX ( 'All Patterns'[MIN_LAST_MODIFIED] ),
            ALLEXCEPT ( 'All Patterns', 'All Patterns'[OPP_ID] )
        ),
    1
)

     

     Now your measures can be greatly simplified. The first one doesn't even need to use the calculated column:

    Pattern Count.ALLOppID = DISTINCTCOUNT ( 'All Patterns'[OPP_ID] )

     

    The second one does use the calculated column:

    Pattern Value 2.ALL = 
CALCULATE(
    [Sum Value.ALL],
    'All Patterns'[IsLastTransaction] = 1
)
JirkaZ's avatar
JirkaZ
Solution Specialist
5 years ago

So basically you need to be wise about your DAX. The code that you're using is forcing the engine to evaluate stuff for each row individually (you can check that in DAX studio). So in general you want to avoid that with large datasets.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

JirkaZ Thank you for the advice. What I am struggling with is the current context of the requirements is that I take the most recent status. I understand that it is best practice not to evaluate each row individually but how can I structure the measure to do that? Apologies but I am so lost.