Slow Calculate with distinct count

[Bokazoit]

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?

[OwenAuger]

@Bokazoit 

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?

[Bokazoit]

This is what my chart looks like using Your measure:

🤔

And the measure for Antal betalte donorer:

Antal betalte donorer = CALCULATE(DISTINCTCOUNT(FactBetalingslinjer[DonorNummer]), USERELATIONSHIP(FactBetalingslinjer[DatoKeyBogfoeringsdato], DimDato[DatoKey]))

[OwenAuger · Accepted solution]

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 )
)

[Bokazoit]

You are über smart and skillful! 🙂

What happens?

Is it because of my USERELATIONSHIP or?

It is **bleep** quick now 🙂

[OwenAuger]

👍Glad to hear it 🙂

I believe the main issue was FILTER ( ALL ( DimDato...) )

Firstly, it's more efficient to apply a filter to a single column (in this case DimDato[Dato]) than the entire unfiltered table ALL ( DimDato ).

Also, iterating row-by-row with FILTER always runs the risk of performing poorly. In this example we have an alternative, which is to use DATESINPERIOD to generate the required filter more efficiently by specifying a one-year period ending on the maximum date.

A secondary issue was using table functions LASTDATE and SAMEPERIODLASTYEAR to produce scalar values. I would generally only use these functions if I need to apply the result as a filter, and it turns out we can avoid these in this case anyway.

USERELATIONSHIP itself wasn't an issue as far as I can see - it simply activated the required relationship but wasn't the main performance issue.

Regards,

Owen

[Bokazoit]

Thx and makes sense I was actually looking for some way to filter on one column and not all as You describe, but did not figure it out. And the datesperiod You used makes sense for me too. I became much wiser I hope with You help 🙂

[Bokazoit]

Yes each month in the Chart shown, looks 12 month back from the month shown, so that:

February 2022 counts from 20210301 to 20220228

January 2022 counts from 20210201 to 20220131

December 2021 counts from 20210101 to 20211231

etc.

[timg]

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

[Bokazoit]

I 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.