Measure that counts in second column

[MichaelH78]

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	City 2
2	City 2	City 1
3	City 1	City 3
4	City 1
5	City 3	City 1
6	City 2	City 3

Now I am looking for a measure that determines the number of data records that have the city in their primary or secondary residence. The first column in the matrix visual (or in the Excel pivot table) contains a list of primary residence locations.

City	Count	Explanation
City 1	5	3 times primary, 2 times secondary
City 2	3	2 times primary, 1 time secondary
City 3	3	1 time primary, 2 times secondary

Thank you
Michael

[v-jiewu-msft]

Hi @MichaelH78 ,

First of all, many thanks to  for your very quick and effective replies.

Based on my testing, please try the following methods:

1.Create the simple table.

2.Create the new measure to calculate the number of city primary residence.

Count primary city = 
var cit_ = SELECTEDVALUE('Table city'[City])
RETURN
COUNTROWS(FILTER('Table', 'Table'[Primary residence (city)] = cit_))

Count secondary city = 
var cit_ = SELECTEDVALUE('Table city'[City])
RETURN
COUNTROWS(FILTER('Table', 'Table'[Secondary residence (city)] = cit_))

3.Create the new measure to calculate the count.

Count = [Count primary city] + [Count secondary city]

4.Drag the category field into the slicer.

5.The result is shown below.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MichaelH78]

Hi Wisdom Wu,

hi, thanks for your help. This looks very good and also works in Power BI.
Before I transfer everything to Power BI: Does this also work in Excel? Power Pivot in Excel does not recognise SELECTEDVALUE. You can probably do something with HASONEVALUE, but I can't manage that 😉

Best regards

Michael

[MichaelH78 · Accepted solution]

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]

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]

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