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

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.