Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello,
I am trying to calculate the distribution of articles (or article groups) in a dynamic timespan.
Currently I am using a DAX Statement which underperforms massively with a large number of articles or dates:
Calculate(distinctount('Dim Shops'[Shop]); filter('Fakt Transactions'; 'Fact Transactions'[Amount] > 0))
The statement returns the correct value, however the runtime can easily exceed 1 Minute or more.
Does anybody have an idea how to boost performance on such statements?
Thank you
Justus
Solved! Go to Solution.
Thanks for your help.
However, I found a formula that calculates the correct result in far less time.
It goes like this:
CALCULATE (DISTINCTCOUNT('Dim Shops'[Shop]); VALUES([Fact Transactions]))
Thanks for your help.
However, I found a formula that calculates the correct result in far less time.
It goes like this:
CALCULATE (DISTINCTCOUNT('Dim Shops'[Shop]); VALUES([Fact Transactions]))
Hi @Jayshamone ,
This formula is not complicated, it seems that there is no room for improvement.
The following reasons may cause the query to be too slow:
1. The dataset is too large.
2. Connection mode.
What's your connection mode?
The time that's needed to refresh the visualization depends on the performance of the underlying data source if you are using 'DirectQuery'.
3. The local computer configuration is too low.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
how large is your table?
And how does the Dim Shops table relate to the Fact Transaction table?
Have you tried it without Filter?
Calculate ( distinctount('Dim Shops'[Shop]); Fact Transactions'[Amount] > 0 )
The Transactions Table has about 60 Mio entries, The Trans_Aggregate Table which relates to the Shops Table has about 20 Mio Entries.
The formula returns a false - every article has the same distribution then - value without filter. This does not work unfortunately
@Jayshamone , refer
https://www.sqlbi.com/articles/analyzing-distinctcount-performance-in-dax/
https://community.powerbi.com/t5/Desktop/Slow-calculations-with-DISTINCTCOUNT/td-p/450003
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
86 | |
82 | |
64 | |
63 | |
56 |
User | Count |
---|---|
171 | |
113 | |
110 | |
73 | |
73 |