Forum Discussion
Continued ContributorSlow Calculate with distinct count
My Facttable consist of 7 million rows and is in that context relatively small. We have a distinct count on a column with member numbers which is okey, but not lightning fast. What changed our view on this measure was when we needed to answer and represent the answer in a chart. The question is:
"If we are in February 2022, what is the amount of unique members 12 month back? If we are in January 2022, what is the amount of unique members 12 month back? etc."
That should give us this chart:
Problem:
It is insanely slow. I am aware that I for each month do a distinct count 12 month back, but 25 seconds slow!
The measure:
Measure =
VAR Maxdate = LASTDATE(DimDato[Dato] )
VAR Mindate = SAMEPERIODLASTYEAR(Maxdate)
VAR Result =
CALCULATE([Antal betalte donorer],
FILTER(ALL(DimDato), DimDato[Dato] <= Maxdate && DimDato[Dato] > Mindate ))
Return Result
We have tried different approaches and asked for help in here, but the speed is the still slow as h..l
We use pro licens, and are now considdering moving to an Azure SSAS solution. But is that really necessary? I would expect 7 mio. rows to be nothing but apparently not! What to do?
Thanks - it looks like my earlier assumption "DimDato is marked as a date table, or the DimDato[Dato] column is on the one-side of a relationship." doesn't hold. As a result, the filters on DimDato are not being removed automatically when a filter is applied to the column DimDato[Dato].
For my measure to work, DimDato should be marked as a Date table with DimDato[Dato] being selected as the date column (usual setup for time intelligence functions).
Alternatively, you could just rewrite the measure as:
Measure = CALCULATE( [Antal betalte donorer], DATESINPERIOD ( DimDato[Dato], MAX ( DimDato[Dato] ), -1, YEAR ), REMOVEFILTERS ( DimDato ) )
11 Replies
- timg
Solution Sage
Hi Bokazoit,
You could try using COUNTROWS instead of DISTINCTCOUNT. This method generally has much less impact on performance. In this article you can see some examples: Optimizing the performance of DISTINCTCOUNT in DAX - Gorilla BI.
Best regards,
Tim
BokazoitI tried that and yes it made a small impact in the performance, but no near acceptable. My problem is what I can do in this matter. Is it the pro user licens that makes it slow and will a premium user solve it or will Azure SSAS make the difference?
I would not expect power bi thrown to its knees over 7 mio. rows and distinct count.
- OwenAuger
Super User
I'm not sure how much difference this will make, but you can simplify things a bit by avoiding unnecessary table functions (in Maxdate & Mindate) as well as applying a filter just to the column DimDato[Dato].
BTW I'm assuming DimDato is marked as a date table, or the DimDate[Dato] column is on the one-side of a relationship.
Then try:
Measure = CALCULATE( [Antal betalte donorer], DATESINPERIOD ( DimDato[Dato], MAX ( DimDato[Dato] ), -1, YEAR ) )This should help a bit, but it may be that [Antal betalte donorer] itself needs optimization.
Regards,
Owen
- Bokazoit
That measure do not count distinct over last 12 month as needed as I tried to describe in my initial post with the question and answer as well as the chart to show the result.
The measure should for each month count distinct members 12 month back.
- OwenAuger
Interesting...the measure I posted should produce identical results to the measure you posted. (comparison of measures with same structure https://dax.do/drM0jkPkJD6b4T/)
🤷
In any case, my main point was about avoiding using table functions to produce scalar values, and avoiding filtering a table when you can filter a column.
By "12 months back" you mean "over the 12 month period ending on the maximum date visible in the current filter context" don't you?
