Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I have a table with entries for different products (with unique ID) over different years. In each row I have ID, Year and two different ages. Currently I am linking the two ages with two support tables where an age is linked with and age band: e.g. 0-10 years, 10-20 years, etc. so I can represent the data by age band without computing the relevant age band within the database. However, if I want to plot/compare the two different ages (by age band) side-by side it's impossible as I would have two different "x axis/legends".
Is there a way to summarize Ages by age band in a new table (considering that I would like to keep the possibility to choose the relevant year?) Thanks!
A Sample File here with a limited set of data. The result I'd like to achieve is in the "result" sheet: I downloaded aggregated data, put in excel and loaded back in PBI to achieve this. I'd simply like to have an automated way - retaining the possibility to switch between years - to compute it in PBI.
Solved! Go to Solution.
Hi @giogiogio ,
You can do this without the need for the Ageband table.
On your Database unpivot your two Age columns this will give you two columns one witht the Attribute and value.
Create a new table with the age band you need:
AgeBandMinimumMaximum
0-10 | 0 | 9,99 |
10-20 | 10 | 19,99 |
20-30 | 20 | 29,99 |
30-40 | 30 | 39,99 |
+40 | 40 | 9999 |
Now create the following measure:
Count by age =
VAR temnp_table =
FILTER (
DataBase_Unpivot,
DataBase_Unpivot[Value] <= MAX ( Ageband[Maximum] )
&& DataBase_Unpivot[Value] >= MIN ( Ageband[Minimum] )
)
RETURN
COUNTROWS ( temnp_table )
If the Unpivot of the columns is not an option beside the age band table create one for the ages:
Ages
Age1 |
Age2 |
Now create the followin measure:
Count by age no unpivot =
SWITCH (
SELECTEDVALUE ( Ages[Ages] ),
"Age1",
COUNTROWS (
FILTER (
DataBase,
Database[Age] <= MAX ( Ageband[Maximum] )
&& Database[Age] >= MIN ( Ageband[Minimum] )
)
),
"Age2",
COUNTROWS (
FILTER (
DataBase,
Database[Age 2] <= MAX ( Ageband[Maximum] )
&& Database[Age 2] >= MIN ( Ageband[Minimum] )
)
)
)
Check PBIX file with both options
I have kept your model for comparision but has refered no need to keep the age bands for both ages, also be aware that the two tables are not related with the other tables.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank you both @MFelix & @v-easonf-msft .
I had to use the second solution from @MFelix as the actual data are coming from a DAX code. The PQ code is really interesting too, thanks to @v-easonf-msft
Hi, @giogiogio
It seems that your data is from excel. If so ,you can also directly create such result table in PQ .
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZNBroMwDETvwrpCJHbs+CxV73+NQtyQGfRXf2WhB3kZY7/fW9leWz2Kn6XEPp5s+7xu0EdJ4AjiKiVBB1CPq9QEMUBdDrW9zQLosqgnckaXR3uiTmiYNBLFDwm4dFzidgm4WiJnFPl6QZeAyxNNl1IuQ5dSLkOXUi5Dl1IuQ1dbLnn0sC2XPHrYlksePWzLJY8eGrh0D3QZuFoiZxR5w0CXgcsTTZeDq3EuB5dxrv991ekryhWAfJc/UM8eCh4YEDkSdUL5K49EM3I5QNZ5bibreSQNzmSRZ9Lk/Fj6Co9OgR1X2Sv5YM3Pfankg00/F6aSD5b9PKzevs8X", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Snapshot Year" = _t, Age = _t, #"Age 2" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Snapshot Year", Int64.Type}, {"Age", type number}, {"Age 2", type number}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Age Band", each if [Age] < 10 then "0-10" else if [Age] < 20 then "10-20" else if [Age] < 30 then "20-30" else if [Age] < 40 then "30-40" else if [Age] >= 40 then "40+" else null),
#"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Age Band2", each if [Age 2] < 10 then "0-10" else if [Age 2] < 20 then "10-20" else if [Age 2] < 30 then "20-30" else if [Age 2] < 40 then "30-40" else if [Age 2] >= 40 then "40+" else null),
#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column1",{ "Age", "Age 2"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Age Band", "Age1"},{"Age Band2", "Age2"}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Renamed Columns1", {"ID", "Snapshot Year"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Age"}}),
#"Grouped Rows" = Table.Group(#"Renamed Columns", {"Age", "Value", "Snapshot Year"}, {{"Count", each Table.RowCount(Table.Distinct(_)), Int64.Type}}),
#"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Value]), "Value", "Count", List.Sum),
#"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Snapshot Year", Order.Ascending}, {"Age", Order.Ascending}})
in
#"Sorted Rows"
Please check my pbix file for more details.
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @giogiogio ,
You can do this without the need for the Ageband table.
On your Database unpivot your two Age columns this will give you two columns one witht the Attribute and value.
Create a new table with the age band you need:
AgeBandMinimumMaximum
0-10 | 0 | 9,99 |
10-20 | 10 | 19,99 |
20-30 | 20 | 29,99 |
30-40 | 30 | 39,99 |
+40 | 40 | 9999 |
Now create the following measure:
Count by age =
VAR temnp_table =
FILTER (
DataBase_Unpivot,
DataBase_Unpivot[Value] <= MAX ( Ageband[Maximum] )
&& DataBase_Unpivot[Value] >= MIN ( Ageband[Minimum] )
)
RETURN
COUNTROWS ( temnp_table )
If the Unpivot of the columns is not an option beside the age band table create one for the ages:
Ages
Age1 |
Age2 |
Now create the followin measure:
Count by age no unpivot =
SWITCH (
SELECTEDVALUE ( Ages[Ages] ),
"Age1",
COUNTROWS (
FILTER (
DataBase,
Database[Age] <= MAX ( Ageband[Maximum] )
&& Database[Age] >= MIN ( Ageband[Minimum] )
)
),
"Age2",
COUNTROWS (
FILTER (
DataBase,
Database[Age 2] <= MAX ( Ageband[Maximum] )
&& Database[Age 2] >= MIN ( Ageband[Minimum] )
)
)
)
Check PBIX file with both options
I have kept your model for comparision but has refered no need to keep the age bands for both ages, also be aware that the two tables are not related with the other tables.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |