Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Summarizing & reworking Data

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 sup...
Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    Hi Anonymous ,

     

    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.

v-easonf-msft's avatar
v-easonf-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi, Anonymous 

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.