Forum Discussion

Bokazoit's avatar
Bokazoit
Continued Contributor
4 years ago
Solved

Slow 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 o...
Need Help
  • OwenAuger's avatar
    OwenAuger
    4 years ago

    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's avatar
Bokazoit
Continued Contributor
4 years ago

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.