Forum Discussion

Bokazoit's avatar
Bokazoit
Icon for Continued Contributor rankContinued 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 )
)

     

     

OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper User
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
Icon for Continued Contributor rankContinued Contributor
4 years ago

You are über smart and skillful! 🙂

What happens?

Is it because of my USERELATIONSHIP or?

It is **bleep** quick now 🙂

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    4 years ago

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

      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 🙂