topic Re: Measure that counts in second column in DAX Commands and Tips

Measure that counts in second column

MichaelH78 — Mon, 03 Jun 2024 21:13:35 GMT

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

Re: Measure that counts in second column

lbendlin — Mon, 03 Jun 2024 22:52:53 GMT

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.

Re: Measure that counts in second column

MichaelH78 — Tue, 04 Jun 2024 06:09:51 GMT

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

Re: Measure that counts in second column

Anonymous — Tue, 04 Jun 2024 06:54:30 GMT

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.

Re: Measure that counts in second column

MichaelH78 — Tue, 04 Jun 2024 08:09:55 GMT

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

Re: Measure that counts in second column

MichaelH78 — Tue, 04 Jun 2024 21:00:29 GMT

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 😉