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

     

     

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.

  • 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 🙂