Measure or Calculated Column

[ArchStanton]

Hi,

I have the following calculated column that I use in my report, it iterates over a Faact table that has 54,000 rows and counting and 80 columns.

Age Profile3 = 
if( 'Cases'[Case Length (Adj)] < 91.3, REPT(UNICHAR(8203),21)&" 0-3 Mths",
    if('Cases'[Case Length (Adj)] < 182.6, REPT(UNICHAR(8203),20)&" 3-6 Mths",
        if('Cases'[Case Length (Adj)] < 274, REPT(UNICHAR(8203),19)&" 6-9 Mths",
            if('Cases'[Case Length (Adj)] < 365.25, REPT(UNICHAR(8203),18)&" 9-12 Mths",
                if('Cases'[Case Length (Adj)] < 457, REPT(UNICHAR(8203),17)&" 12-15 Mths",
                    if('Cases'[Case Length (Adj)] < 547.9, REPT(UNICHAR(8203),16)&" 15-18 Mths",
                        if('Cases'[Case Length (Adj)] < 639.19, REPT(UNICHAR(8203),15)&" 18-21 Mths",
                            if('Cases'[Case Length (Adj)] < 730.5, REPT(UNICHAR(8203),14)&" 21-24 Mths",
                                if('Cases'[Case Length (Adj)] < 821.9, REPT(UNICHAR(8203),13)&" 24-27 Mths",
                                    if('Cases'[Case Length (Adj)] < 913.2, REPT(UNICHAR(8203),12)&" 27-30 Mths",
                                        if('Cases'[Case Length (Adj)] < 1004.5, REPT(UNICHAR(8203),11)&" 30-33 Mths",
                                            if('Cases'[Case Length (Adj)] < 1095.8, REPT(UNICHAR(8203),10)&" 33-36 Mths",
                                                if('Cases'[Case Length (Adj)] < 1187.1, REPT(UNICHAR(8203),9)&" 36-39 Mths",
                                                    if('Cases'[Case Length (Adj)] < 1278.4, REPT(UNICHAR(8203),8)&" 39-42 Mths",
                                                        if('Cases'[Case Length (Adj)] < 1369.7, REPT(UNICHAR(8203),7)&" 42-45 Mths",
                                                            if('Cases'[Case Length (Adj)] < 1461, REPT(UNICHAR(8203),6)&" 45-48 Mths",
                                                                if('Cases'[Case Length (Adj)] < 1552.4, REPT(UNICHAR(8203),5)&" 48-51 Mths",
                                                                    if('Cases'[Case Length (Adj)] < 1643.7, REPT(UNICHAR(8203),4)&" 51-54 Mths",
                                                                        if('Cases'[Case Length (Adj)] < 1735, REPT(UNICHAR(8203),3)&" 54-57 Mths",
                                                                            if('Cases'[Case Length (Adj)] < 1826.3, REPT(UNICHAR(8203),2)&" 57-60 Mths",
                                                                                REPT(UNICHAR(8203),1)&" 60+ Mths"))))))))))))))))))))

 My data model is very complicated and everything runs slowly due to the number of calculated columns I have.

I have been asked to add this calculated column that uses "Case Length using Validation Date" instead of "Case Length Adj".

I am concerned that this will be very expensive and slow down things even more - could I do this with a Measure and if so how?
Thanks,

[grazitti_sapna · Accepted solution]

Hi @ArchStanton,

I would suggest to create a separate table like this 

AgeBands =
DATATABLE(
"AgeGroup", STRING,
"MinDays", DOUBLE,
"MaxDays", DOUBLE,
{
{"0-3 Mths", 0, 91.3},
{"3-6 Mths", 91.3, 182.6},
{"6-9 Mths", 182.6, 274},
{"9-12 Mths", 274, 365.25},
{"12-15 Mths", 365.25, 457},
{"15-18 Mths", 457, 547.9},
{"18-21 Mths", 547.9, 639.19},
{"21-24 Mths", 639.19, 730.5},
{"24-27 Mths", 730.5, 821.9},
{"27-30 Mths", 821.9, 913.2},
{"30-33 Mths", 913.2, 1004.5},
{"33-36 Mths", 1004.5, 1095.8},
{"36-39 Mths", 1095.8, 1187.1},
{"39-42 Mths", 1187.1, 1278.4},
{"42-45 Mths", 1278.4, 1369.7},
{"45-48 Mths", 1369.7, 1461},
{"48-51 Mths", 1461, 1552.4},
{"51-54 Mths", 1552.4, 1643.7},
{"54-57 Mths", 1643.7, 1735},
{"57-60 Mths", 1735, 1826.3},
{"60+ Mths", 1826.3, 99999}
}
)

and create a relationship with your actual table and use this in your visual, you can filter 0-3mth, 3-6mth age groups from the filter pane.

[Kedar_Pande]

@ArchStanton 

Create a separate Age Profile dimension table instead. Load it with all the age ranges and their corresponding day thresholds.

Then create a relationship to your Cases table and use this measure:

Age Profile =
LOOKUPVALUE(
'Age Profile'[Profile],
'Age Profile'[Min Days],
0,
'Age Profile'[Max Days],
SELECTEDVALUE('Cases'[Case Length (Adj)])
)

[pankajnamekar25]

Hello @ArchStanton 

When to Use a Calculated Column
If you need row-level attributes that behave like part of your table.
For relationships, slicers, filters directly from the Fields list.
Downside → Each column adds to model size & refresh time (big issue with 54k × 80).

When to Use a Measure
If you only need the Age Banding for visuals, grouping, or displaying text, not for joins.
Measures are lighter, don’t bloat PBIX, and evaluate on the fly.

In your case
If “Age Profile” is just for row headers or grouping in a Matrix, you can create a separate Age Band dimension table (with min/max ranges) and relate it.

Or use a Measure with SWITCH(TRUE()) to classify dynamically (faster & more maintainable than nested IFs).

Measure (lighter, preferred if no relationship needed):
Age Profile =
VAR CaseLen = MAX ( 'Cases'[Case Length using Validation Date] )
RETURN
SWITCH(
TRUE(),
CaseLen < 91.3, "0–3 Mths",
CaseLen < 182.6, "3–6 Mths",
CaseLen < 274, "6–9 Mths",
CaseLen < 365.25, "9–12 Mths",
CaseLen < 457, "12–15 Mths",
CaseLen < 547.9, "15–18 Mths",
CaseLen < 639.19, "18–21 Mths",
CaseLen < 730.5, "21–24 Mths",
CaseLen < 821.9, "24–27 Mths",
CaseLen < 913.2, "27–30 Mths",
CaseLen < 1004.5, "30–33 Mths",
CaseLen < 1095.8, "33–36 Mths",
CaseLen < 1187.1, "36–39 Mths",
CaseLen < 1278.4, "39–42 Mths",
CaseLen < 1369.7, "42–45 Mths",
CaseLen < 1461, "45–48 Mths",
CaseLen < 1552.4, "48–51 Mths",
CaseLen < 1643.7, "51–54 Mths",
CaseLen < 1735, "54–57 Mths",
CaseLen < 1826.3, "57–60 Mths",
"60+ Mths"
)

If my response helped you, please consider clicking
Accept as Solution ✅ and giving it a Like 👍 – it helps others in the community too.


Thanks,


Connect with me on:

LinkedIn

[ArchStanton]

Really appreciate your advice - thanks!

[GrowthNatives]

Hi @ArchStanton ,
you are in a classic Calculated Column vs Measure dilemma in Power BI with performance impact.

 Current Situation

• You’re using a calculated column with nested IF conditions to bucket Case Length (Adj) into age bands (0–3 Mths, 3–6 Mths, etc.).

• Your fact table has 54,000 rows × 80 columns → calculated columns increase memory size (slower refresh, larger PBIX).

• Concern: Adding another column (Case Length using Validation Date) will further slow the model.
  
  

General Rule

• Calculated Columns → stored in the model, increase size, evaluated at refresh time.

• Measures → evaluated at query time, don’t increase model size, much lighter.

✅ If you only need the Age Profile for visuals, slicers, or grouping, use a Measure.
❌ If you need it as a row-level attribute (for joins, relationships, filters in other tables), then you need a column.

Recommended Measure

You can rewrite your Age Profile as a Measure. Example:

Age Profile =
VAR CaseLen = SELECTEDVALUE ( 'Cases'[Case Length (Adj)] )
RETURN
    SWITCH(
        TRUE(),
        CaseLen < 91.3, "0–3 Mths",
        CaseLen < 182.6, "3–6 Mths",
        CaseLen < 274, "6–9 Mths",
        CaseLen < 365.25, "9–12 Mths",
        CaseLen < 457, "12–15 Mths",
        CaseLen < 547.9, "15–18 Mths",
        CaseLen < 639.19, "18–21 Mths",
        CaseLen < 730.5, "21–24 Mths",
        CaseLen < 821.9, "24–27 Mths",
        CaseLen < 913.2, "27–30 Mths",
        CaseLen < 1004.5, "30–33 Mths",
        CaseLen < 1095.8, "33–36 Mths",
        CaseLen < 1187.1, "36–39 Mths",
        CaseLen < 1278.4, "39–42 Mths",
        CaseLen < 1369.7, "42–45 Mths",
        CaseLen < 1461, "45–48 Mths",
        CaseLen < 1552.4, "48–51 Mths",
        CaseLen < 1643.7, "51–54 Mths",
        CaseLen < 1735, "54–57 Mths",
        CaseLen < 1826.3, "57–60 Mths",
        "60+ Mths"
    )

Why This Is Better

• No column storage → smaller PBIX size.

• Runs dynamically only when used in a visual.

• Much easier to maintain (you can replace those long nested IFs with SWITCH(TRUE(),...)).
  
  

When to Still Use a Column

• If Age Profile is needed for relationships, row-level filters, or as a slicer directly from the field list, you’ll need it as a calculated column.

• Otherwise, use the measure approach above.
  
  

✅ Best Practice:

• Keep measures for logic, columns only for keys/relationships.

• If you must have a column, consider building an Age Bucket Dimension table and relate it instead of hardcoding.

⭐Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!
🚀Let’s keep building smarter, data-driven solutions together!🚀 [Explore More]

[ArchStanton]

Hi, I don't have an obvious column that I could link an Age Bucket Dimension Table to in my Fact table.

My Age Bucket Table looks like this:

My fact table does not have AgeGroup - to have one I would need to build a calculated column.

I have the Application Validation Date (AVD) and then a Case Length column that counts the amount of days between the AVD and Today

[ArchStanton]

Thank you so much for this!!

[grazitti_sapna]

Hi @ArchStanton,

Definitely you should go with measure instead of calculated column, as it will consume memory as well as space.

change your if statementes to case switch statement as below

Age Profile3 Measure =
VAR CaseLength = MAX('Cases'[Case Length using Validation Date])
RETURN
SWITCH(
TRUE(),
CaseLength < 91.3, REPT(UNICHAR(8203),21) & " 0-3 Mths",
CaseLength < 182.6, REPT(UNICHAR(8203),20) & " 3-6 Mths",
CaseLength < 274, REPT(UNICHAR(8203),19) & " 6-9 Mths",
CaseLength < 365.25, REPT(UNICHAR(8203),18) & " 9-12 Mths",
CaseLength < 457, REPT(UNICHAR(8203),17) & " 12-15 Mths",
CaseLength < 547.9, REPT(UNICHAR(8203),16) & " 15-18 Mths",
CaseLength < 639.19, REPT(UNICHAR(8203),15) & " 18-21 Mths",
CaseLength < 730.5, REPT(UNICHAR(8203),14) & " 21-24 Mths",
CaseLength < 821.9, REPT(UNICHAR(8203),13) & " 24-27 Mths",
CaseLength < 913.2, REPT(UNICHAR(8203),12) & " 27-30 Mths",
CaseLength < 1004.5, REPT(UNICHAR(8203),11) & " 30-33 Mths",
CaseLength < 1095.8, REPT(UNICHAR(8203),10) & " 33-36 Mths",
CaseLength < 1187.1, REPT(UNICHAR(8203),9) & " 36-39 Mths",
CaseLength < 1278.4, REPT(UNICHAR(8203),8) & " 39-42 Mths",
CaseLength < 1369.7, REPT(UNICHAR(8203),7) & " 42-45 Mths",
CaseLength < 1461, REPT(UNICHAR(8203),6) & " 45-48 Mths",
CaseLength < 1552.4, REPT(UNICHAR(8203),5) & " 48-51 Mths",
CaseLength < 1643.7, REPT(UNICHAR(8203),4) & " 51-54 Mths",
CaseLength < 1735, REPT(UNICHAR(8203),3) & " 54-57 Mths",
CaseLength < 1826.3, REPT(UNICHAR(8203),2) & " 57-60 Mths",
REPT(UNICHAR(8203),1) & " 60+ Mths"
)

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[ArchStanton]

Thanks for your help with this.

The only thing is that I need to have the 0-3mth, 3-6mth age groups visible as Row Headers in my Matrix visual.

I think this can only work with a calculcated column or have i got that wrong?

[grazitti_sapna · Accepted solution]

Hi @ArchStanton,

I would suggest to create a separate table like this 

AgeBands =
DATATABLE(
"AgeGroup", STRING,
"MinDays", DOUBLE,
"MaxDays", DOUBLE,
{
{"0-3 Mths", 0, 91.3},
{"3-6 Mths", 91.3, 182.6},
{"6-9 Mths", 182.6, 274},
{"9-12 Mths", 274, 365.25},
{"12-15 Mths", 365.25, 457},
{"15-18 Mths", 457, 547.9},
{"18-21 Mths", 547.9, 639.19},
{"21-24 Mths", 639.19, 730.5},
{"24-27 Mths", 730.5, 821.9},
{"27-30 Mths", 821.9, 913.2},
{"30-33 Mths", 913.2, 1004.5},
{"33-36 Mths", 1004.5, 1095.8},
{"36-39 Mths", 1095.8, 1187.1},
{"39-42 Mths", 1187.1, 1278.4},
{"42-45 Mths", 1278.4, 1369.7},
{"45-48 Mths", 1369.7, 1461},
{"48-51 Mths", 1461, 1552.4},
{"51-54 Mths", 1552.4, 1643.7},
{"54-57 Mths", 1643.7, 1735},
{"57-60 Mths", 1735, 1826.3},
{"60+ Mths", 1826.3, 99999}
}
)

and create a relationship with your actual table and use this in your visual, you can filter 0-3mth, 3-6mth age groups from the filter pane.

[ArchStanton]

Hi

I have done what you suggested but have a problem, I don't know what to link the AgeBands table to? 
I don't have Age Bands in my main Fact table.
The new table has Min Days and Max days - the fact table has a calculated column that shows the length of a case in days so this number could fall anywhere within Min or Max.

I'm not sure how this can work?

[grazitti_sapna]

Hi @ArchStanton,

You don't actually need to create a relationship, just use below DAX and put it in your visuals

AgeBandMeasure =
VAR Days = MAX('Cases'[CaseLengthDays])
RETURN
MAXX(
FILTER(
AgeBands,
Days >= AgeBands[MinDays] &&
Days < AgeBands[MaxDays]
),
AgeBands[AgeGroup]
)

[ArchStanton]

This doesn't work as I need to count the number of cases that fall between each Age Band.

When I used your measure it just shows me the MAX value which is 60mths+

[grazitti_sapna]

Ohkay, got it

Try this 

CasesInAgeBand =
VAR CurrentBandMin = MAX(AgeBands[MinDays])
VAR CurrentBandMax = MAX(AgeBands[MaxDays])
RETURN
CALCULATE(
COUNTROWS('Cases'),
'Cases'[CaseLengthDays] >= CurrentBandMin,
'Cases'[CaseLengthDays] < CurrentBandMax
)

[ArchStanton]

We're 99% there!

I'd like the Total to SUM all of the values.

AgeBandMeasure = 
VAR CurrentBandMin = MAX(AgeBands[MinDays])
VAR CurrentBandMax = MAX(AgeBands[MaxDays])
RETURN
CALCULATE(
COUNTROWS('Cases'),
'Cases'[Case Length (Validation Date)] >= CurrentBandMin,
'Cases'[Case Length (Validation Date)] < CurrentBandMax,
'Cases'[statecode] = "Active"
)

[grazitti_sapna · Accepted solution]

Hi @ArchStanton,

Update your measure like this 

CasesInAgeBand =
SUMX (
AgeBands,
VAR CurrentBandMin = AgeBands[MinDays]
VAR CurrentBandMax = AgeBands[MaxDays]
RETURN
CALCULATE (
COUNTROWS ( 'Cases' ),
'Cases'[CaseLengthDays] >= CurrentBandMin,
'Cases'[CaseLengthDays] < CurrentBandMax
)
)

[ArchStanton]

That's great, thank you!!