Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
grggmrtn
Post Patron
Post Patron

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

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:

grggmrtn_0-1613115354572.png

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.

grggmrtn_1-1613115532266.png

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?

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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:

 

 

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

3 REPLIES 3
OwenAuger
Super User
Super User

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:

 

 

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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 🙂

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

All the best 🙂

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors