Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I am wondering if someone can help me with a very slow DAX calculation.
Business Case:
We consider a customer to be financially active on our system for a financial year if the sum of their transactions for a financial year for any Group ID is greater than 0.
Our financial year lasts from 1st of August and ends 31st of July.
Product ID's are grouped under a Grouping ID.
DAX
I created a grouping calculated column in the customer table, (Fiscal Year || Grouping ID || Customer ID) { to avoid having to create joins in the query step).
I want to calculate the number of financially active customers ( Active Customer Count ) and a cumulative count of this metric (Cumulative Active Customer Count). However the cumulative sum is very slow (20-30 seconds long)
Any idea on what I can do to speed up this cumulative count?
Link to file:
https://drive.google.com/file/d/18OuoPSx2pFmk0Q-NCU1hhPcFyt_pQvr3/view?usp=sharing
Solved! Go to Solution.
Hi @noname0408
Thanks for reaching out to us.
please refer to
(1) FILTER vs CALCULATETABLE: optimization using cardinality estimation - SQLBI
" It is correct to assume that separate filters are better, but if you have many filters and a distinct count calculation, you might try to consolidate filters in order to improve performance. I expect this behavior to be improved in future releases of Power Pivot and Analysis Services Tabular."
(2) Why my Power BI Matrix or Table Visual is SLOW - RADACAD
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Hi @noname0408
Thanks for reaching out to us.
please refer to
(1) FILTER vs CALCULATETABLE: optimization using cardinality estimation - SQLBI
" It is correct to assume that separate filters are better, but if you have many filters and a distinct count calculation, you might try to consolidate filters in order to improve performance. I expect this behavior to be improved in future releases of Power Pivot and Analysis Services Tabular."
(2) Why my Power BI Matrix or Table Visual is SLOW - RADACAD
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
15 | |
10 |
User | Count |
---|---|
57 | |
50 | |
44 | |
21 | |
19 |