Forum Discussion
Measure or Calculated Column
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.
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
)
)
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!
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?
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.
That's great, thank you!!
