Forum Discussion
Advice for Filtering Large Data
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 )
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
)AlexisOlson Thank you! I am curious on how a calculated column worked so well vs. measure. I was always under the impression that measures should be considered first. Regardless, your advice helped and provided relief to a stressful problem. So much gratitude for your support!
Your impression is fairly accurate in general. Calculated columns are a good idea for situations where
- The column values do not depend on filter context. (Calculated columns are not dynamic.)
- The column values store values that would otherwise need to be repeatedly calculated.
With regard to #1, note that this approach would not work if you needed to filter your dates with a slicer and find the maximal date within that filter context. If you need that flexibility, you would need all measures (which could still be much more efficient than what you had).