You may have an age banding dimension (DimAgeBand) in your BI world, your biggest challenge will be adding
new age band to the current dimension dynamically with the minimum amount of changes.
Age Banding Overview
Grouping an age range is basically called "Age Banding", every business or analysts looks at the age banding dimension form a different angle (or point of view), they like to analyze the age in different groups or [AgeBand].
In your DimAgeBand-ing you will have an [Age] or [AgeID] field that will play the role of your unique key and other fields that represents the business requirement like [AgeBand1] and [AgeBand2].
Top 20 and bottom 20 records.
The Code In M Language
Use the mentioned M Language code in a "Blank Query" in Power BI to define the business age band.
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
let
// Make an Age Range, Max number is 127 records else if needed more records you must change the data type from Int8 to Int16
Source = {-1.. 127},
// Convert List to table
ConvertedToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
// Rename field to [Age]
RenamedColumns = Table.RenameColumns(ConvertedToTable,{{"Column1", "Age"}}),
// Change the data type of the field [Age] to Int8 (-127 ... +127)
ChangeDataType= Table.TransformColumnTypes(RenamedColumns ,{{"Age", Int8.Type}}),
// Add AgeBand1
AddedAgeBand1Column = Table.AddColumn(ChangeDataType, "AgeBand1", each
if -1 = [Age] then "Unknown"
else if 0 <= [Age] and [Age] < 18 then "Under 18"
else if 18 <= [Age] and [Age] <= 24 then "[18-24]"
else if 25 <= [Age] and [Age] <= 34 then "[25-34]"
else if 35 <= [Age] and [Age] <= 44 then "[35-44]"
else if 45 <= [Age] and [Age] <= 54 then "[45-54]"
else if 55 <= [Age] and [Age] <= 64 then "[55-64]"
else if 65 <= [Age] and [Age] <= 74 then "[65-74]"
else if 75 <= [Age] and [Age] <= 84 then "[75-84]"
else if 85 <= [Age] and [Age] <= 94 then "[85-94]"
else if 95 <= [Age] and [Age] <= 104 then "[95-104]"
else if 105 <= [Age] and [Age] <= 114 then "[105-114]"
else if 115 <= [Age] and [Age] <= 124 then "[115-124]"
else "[125 +"),
// Add AgeBand2
AddedAgeBand2Column = Table.AddColumn(AddedAgeBand1Column, "AgeBand2", each
if -1 = [Age] then "Unknown"
else if 0 <= [Age] and [Age] < 20 then "Under 20"
else if 20 <= [Age] and [Age] <= 29 then "[20-29]"
else if 30 <= [Age] and [Age] <= 39 then "[30-39]"
else if 40 <= [Age] and [Age] <= 49 then "[40-49]"
else if 50 <= [Age] and [Age] <= 59 then "[50-59]"
else if 60 <= [Age] and [Age] <= 69 then "[60-69]"
else if 70 <= [Age] and [Age] <= 79 then "[70-79]"
else if 80 <= [Age] and [Age] <= 89 then "[80-89]"
else if 90 <= [Age] and [Age] <= 99 then "[90-99]"
else if 100 <= [Age] and [Age] <= 109 then "[100-109]"
else if 110 <= [Age] and [Age] <= 119 then "[110-119]"
else "[120 +")
in
AddedAgeBand2Column
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
What was so Dynamic?
In the above M code you can add
new age banding [AgeBand3] as required by the business, without changing anything in your Power BI.
Reference
https://powerbi.microsoft.com/en-us/
