Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I have the following DAX measure used within a parameter, that is used as a selection panel for a data matrix:
Solved! Go to Solution.
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 )
)
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.
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 🙂
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
15 | |
14 | |
12 | |
9 |