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!!
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?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]
)