power bi column count of rows based on string

Question

I'm trying to achieve something simple, but I can't get my head around it. I have a table with many rows and in one particular column, rows have the same value. I would need an additional column to give me the number of occurrences for each row value. Example:

id  | name     | city     | additionalColumn
1   | Chris    | London   | 6
2   | Jane     | London   | 6
...
64  | Jerry    | Glasgow  | 3

Basically, in that table, I have 6 names with London as the city. I want each row where city = London to have the number of occurrences in the additionalColumn.

How do I go about this? I tried this, but it gives me the total number of rows instead, which defeats the purpose. I can't seem to reference the current row, for some reason, just the entire column.

additionalColumn = COUNTROWS(FILTER(table, FIND(table[city], table[city],,0)>0))

The result of this is:

id  | name     | city     | additionalColumn
1   | Chris    | London   | 64
2   | Jane     | London   | 64
...
64  | Jerry    | Glasgow  | 64

Any ideas?

Thank you

RADO RADO · Accepted Answer · 2019-06-07T11:48:28

Try:

additionalColumn =
CALCULATE ( COUNTROWS ( MyTable ), ALLEXCEPT ( MyTable, MyTable[city] ) )

How it works: DAX iterates your table record by record. Inside each record, we need to "see" the entire table, while preserving the current city, This is accomplished by using ALLEXCEPT - we tell DAX to show us the entire table (ALL), except the city (we only want to see the city of the record we are iterating. As a result, we will see the entire table filtered by the current city (i.e, for London we will see only 6 records where city is London).

power bi column count of rows based on string

1 Answers