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

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

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