Forum Discussion

MichaelH78's avatar
MichaelH78
Frequent Visitor
2 years ago
Solved

Measure that counts in second column

Hi, I have a table with three columns: - Person number - Primary residence (city) - Secondary residence (city) Person number Primary residence (city) Secondary residence (city) 1 City 1 ...
  • MichaelH78's avatar
    MichaelH78
    2 years ago

    I have solved it simply: With an additional column in data, which calculates the number per primary residence location, but in primary and secondary residence. This means that all rows with the same location always contain the same value.
    The measure then only needs to add a MAX (or MIN or AVG) to the new column 😉

lbendlin's avatar
lbendlin
Super User
2 years ago

Why should that be a measure?  Sounds like this data is immutable. So it can be done in Power Query.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtKs7PU8grzU1KLVLSUQooysxNLKpUKEotzkxJzUtOVdBIziyp1ARKBacm5+elYJWM1YlWMgQqcQbyFOAMI7C4EYILVwASN8ZQbwwWN0EWVwALmSJUoBhhhmE00IhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Person number"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Value"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

 

 

  • MichaelH78's avatar
    MichaelH78
    Frequent Visitor
    2 years ago

    Hello lbendlin,
    Thank you for your reply. I have omitted some (many) columns in the sample data. So the data is mutable. It could be that the persons are filtered e.g. by year of birth or by gender etc., in which case the number should still be calculated correctly. As I understand it, I can only do this with a measure, right?

    Best regards

    Michael