cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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:

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?

1 ACCEPTED SOLUTION
Super User

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
Blog
3 REPLIES 3
Super User

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
Blog
Post Patron

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 🙂

Super User

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
Blog

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors