DAX Article Distribution

[Jayshamone]

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

[Jayshamone · Accepted 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]))

[v-lionel-msft]

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.

[ThoSch-Ger]

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 )

[Jayshamone]

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

[amitchandak]

@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

https://community.powerbi.com/t5/Desktop/DAX-DISTINCTCOUNT-Performance-Aggregrate-vs-Line-Level/td-p...