Forum Discussion
Solved
Parameter Measure Speed Improvement - using Calculate/DistinctCount/Filter...
I have the following DAX measure used within a parameter, that is used as a selection panel for a data matrix: No of Orders = CALCULATE(DISTINCTCOUNT('UK Sales TOTAL'[Doc Number]),FILTER('UK ...
FILTER('UK Sales TOTAL','UK Sales TOTAL'[Sales Value]>0)That's what's causing it to be slow. The golden rule of DAX says: Never filter a table when you can filter a column.
I assume you know the correct rules of DAX formatting and Sales Value is a field in the table, not a measure. If this is so, then you can try:
No of Orders = CALCULATE( DISTINCTCOUNT( 'UK Sales TOTAL'[Doc Number] ), KEEPFILTERS( 'UK Sales TOTAL'[Sales Value] > 0 ) )
FILTER('UK Sales TOTAL','UK Sales TOTAL'[Sales Value]>0)That's what's causing it to be slow. The golden rule of DAX says: Never filter a table when you can filter a column.
I assume you know the correct rules of DAX formatting and Sales Value is a field in the table, not a measure. If this is so, then you can try:
No of Orders =
CALCULATE(
DISTINCTCOUNT( 'UK Sales TOTAL'[Doc Number] ),
KEEPFILTERS( 'UK Sales TOTAL'[Sales Value] > 0 )
)That worked perfectly! Thank you!
I'll make sure to remember that rule in future 🙂
