Need help with a measure in a measure, dynamic age grouping - VERY slow...

[grggmrtn]

Just to start, what I'm doing here works, it's just REALLY slow and I'm wondering if there's anything I can do to make it faster.

I have a list of about 9000 people in a dimension with ID (CPR) and Birthday (Foedselsdato). I need to make a line graph, where I show the number of people that are in diverse age groups by month - meaning age groups during the month (dynamic age).

First measure: Number of people with an active service in the chosen time period (or time period on an axis). The active service is if it's between the start date and stop date:

Antal Borgere = 
VAR StartDato =
    MIN ( Dato[Dato] )
VAR SlutDato =
    MAX ( Dato[Dato] )
RETURN
CALCULATE(
    DISTINCTCOUNT('VelfaerdBI v_DIM_Borger'[CPR]),
        FILTER(
            'VelfaerdBI v_FACT_Faelles_Aktivitet', 
            'VelfaerdBI v_FACT_Faelles_Aktivitet'[StartDato] <= SlutDato && 
            'VelfaerdBI v_FACT_Faelles_Aktivitet'[StopDato] >= StartDato 
            )
    )

 Second measure: Age (at the time of the date filter):

Alder = 
INT(
    YEARFRAC(
        MAX('VelfaerdBI v_DIM_Borger'[Foedselsdato]),
        MAX(Dato[Dato])
))

Detached Age group (aldersgruppe) table:

Third measure: number of people per age group:

Antal borgere / alder = 
CALCULATE(
    [Antal Borgere],
    FILTER(
        'VelfaerdBI v_DIM_Borger',
        COUNTROWS(
            FILTER(
                AldersGruppe,
                [Alder] >= AldersGruppe[Min] && [Alder] <= AldersGruppe[Max]
            )
        )
    )
)

My line graph has Year/Month as axis, the third measure as value, and AldersGruppe as Legend.

It works perfectly.

But it takes 10 minutes to show data when the report is loaded. Not to mention the time it takes when the user starts clicking the slicers I have on the page.

Is there anything you all can think of that would help me calculate this quicker?

[OwenAuger · Accepted solution]

Hi @grggmrtn 

After some testing in a dummy model at my end, below is a suggestion (contained in a single measure).

This should at least be an improvement, but further optimization may be possible.

How does it perform in your actual model?

Your requirements for the  Antal borgere / alder measure are interesting as they are a combination of:

• Events in progress
• Dynamic segmentation
• Related distinct count

Antal borgere / alder =
VAR StartDato =
    MIN ( Dato[Dato] )
VAR SlutDato =
    MAX ( Dato[Dato] )
VAR AldersGruppeMin =
    MIN ( Aldersgruppe[Min] )
VAR AldersGruppeMax =
    MAX ( Aldersgruppe[Max] )
RETURN
    CALCULATE (
        SUMX (
            SUMMARIZE (
                'VelfaerdBI v_FACT_Faelles_Aktivitet',
                'VelfaerdBI v_DIM_Borger'[CPR],
                'VelfaerdBI v_DIM_Borger'[Foedselsdato]
            ),
            VAR Alder =
                INT (
                    YEARFRAC (
                        'VelfaerdBI v_DIM_Borger'[Foedselsdato],
                        SlutDato
                    )
                )
            RETURN
                IF (
                    Alder >= AldersGruppeMin
                        && Alder <= AldersGruppeMax,
                    1
                )
        ),
        'VelfaerdBI v_FACT_Faelles_Aktivitet'[StartDato] <= SlutDato,
        'VelfaerdBI v_FACT_Faelles_Aktivitet'[StopDato] >= StartDato
    )

Your original measure was likely slow due in part to filtering of fact table in Antal Borgere and repeated context transition due to nested measure calls (there is an overhead associated with this).

Oh and I would suggest changing the Max of the "<26" group to 25, to avoid overlapping groups.

Possible further improvements I'm thinking of:

• Define date bounds based AldersGruppeMin and AldresGruppeMax, to avoid repeated YEARFRAC calculations
• Include a row count in SUMMARIZE, rather than CPR

Regards,

Owen

[grggmrtn]

Hey @OwenAuger your solution is actually pretty amazing. I just plugged it in to our solution and it's crazy fast, just what I needed!

But thanks for the explanation as well - that's going to help me a LOT in the future 🙂

[OwenAuger]

Hey @grggmrtn - you're very welcome, and I'm glad to hear it worked well with your actual dataset!

All the best 🙂

Owen