Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have been through tons of this forum searching for the answer to this. It seems like a common question, but I am unable to generate the field that I'd like. In the PowerBI query editor, or in the Report View, I'd like to count the occurrences of duplicate values. For example:
Person
a
a
a
b
c
c
d
d
d
d
e
With this column, I'd like to see this happen.
Person Occurrence
a 3
b 1
c 2
d 4
e 1
OR
Person Occurrence
a 3
a 3
a 3
b 1
c 2
c 2
d 4
d 4
d 4
d 4
e 1
Does that make sense? How can I accomplish that?
For anyone coming to this post looking for the solution, it looks like this link has the right idea - https://www.excelguru.ca/blog/2015/12/09/identify-duplicates-using-power-query/
For the first output, you just need a measure as below.
Measure = COUNTROWS(yourTable)
As to the second, you need a index column and a measure as below
Measure 2 = CALCULATE(COUNTA(yourTable[Person]),ALLEXCEPT(yourTable,yourTable[Person]))
See the attached pbix file.
Neither of those worked. I have large amounts of data in these tables, including many many columns. I want to count the occurrences that a name shows up in one column.
Measure = COUNTROWS(myTable) does not have the behavior in your screenshot. Rather, the measure makes a new column with a value of "1" in every row.
Solution two
Measure 2 = CALCULATE(COUNTA(yourTable[Person]),ALLEXCEPT(yourTable,yourTable[Person]))
Creates massive amounts of unneeded rows.
@Johnathon_S wrote:
Neither of those worked. I have large amounts of data in these tables, including many many columns. I want to count the occurrences that a name shows up in one column.
Measure = COUNTROWS(myTable) does not have the behavior in your screenshot. Rather, the measure makes a new column with a value of "1" in every row.
Solution two
Measure 2 = CALCULATE(COUNTA(yourTable[Person]),ALLEXCEPT(yourTable,yourTable[Person]))Creates massive amounts of unneeded rows.
Both shall work for the given sample in your case. While it won't apply to your real case, please post more specific sample.
Here is a more specific example. Sensitive information has been redacted.
As you can see there is quite a lot of data. Many of the fields have been minimized.
A solution I have done was duplicating the query table and using "Group By" in the query editor. However, I want to have that information in just one table if at all possible.
@Johnathon_S wrote:
Here is a more specific example. Sensitive information has been redacted.
As you can see there is quite a lot of data. Many of the fields have been minimized.
A solution I have done was duplicating the query table and using "Group By" in the query editor. However, I want to have that information in just one table if at all possible.
What are those "Group By" columns? You can just put them along with Measure = COUNTROWS(yourTable) to a table visual.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |