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

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

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

        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.