Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Chris_Ruddick
Frequent Visitor

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 Sales TOTAL','UK Sales TOTAL'[Sales Value]>0))
 
Chris_Ruddick_0-1663084680619.png

 

The measure works fine, however whenever this option is selected in the live report, it is incredibly slow to load compared to any of the other options, many of which are also measures.
 
Can anyone provide info on:
 
1 - What element of this measure is causing it to run slow?
 
2-  An alternative formula that can achieve the same result with a faster processing time?
 
It is also causing this to happen when used alongside certain selection options:
 
Chris_Ruddick_0-1663085326654.png

 

1 ACCEPTED SOLUTION
daXtreme
Solution Sage
Solution Sage

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 )
)

View solution in original post

3 REPLIES 3
v-yadongf-msft
Community Support
Community Support

Hi @Chris_Ruddick ,

 

The presence of a large number of unique values for a column affects the calculation of the DISTINCTCOUNT function and the calculation of the filter parameter of the CALCULATE function.

 

You can try not to filter the whole table to refine the filter conditions. Please try following DAX:

No of Orders = CALCULATE(DISTINCTCOUNT('UK Sales TOTAL'[Doc Number]),KEEPFILTERS(FILTER(ALL('UK Sales TOTAL'[Doc Number],'UK Sales TOTAL'[Sales Value]),'UK Sales TOTAL'[Sales Value]>0)))

 

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

daXtreme
Solution Sage
Solution Sage

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 🙂

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.