Forum Discussion

ArchStanton's avatar
ArchStanton
Power Participant
10 months ago
Solved

Measure or Calculated Column

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 ...
Need Help
Show and Tell
  • grazitti_sapna's avatar
    grazitti_sapna
    10 months ago

    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.

  • grazitti_sapna's avatar
    grazitti_sapna
    10 months ago

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

pankajnamekar25's avatar
pankajnamekar25
Super User
10 months ago

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's avatar
    ArchStanton
    Power Participant
    10 months ago

    Really appreciate your advice - thanks!