Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I need to count the number of times each number 1-8 occurs in a column. The rows can contain multiple numbers and I need to count each number individually. For example:
Record | Type ID |
A-001 | 1 |
A-002 | 3 |
A-003 | 451 |
A-004 | 384621 |
A-005 | 2 |
A-006 | 654 |
This is what I am hoping to achieve
Type ID | Count of Type ID |
1 | 3 |
2 | 2 |
3 | 2 |
4 | 3 |
5 | 2 |
6 | 2 |
7 | 0 |
8 | 1 |
There are thousands of rows. Each Type ID corresponds to a label from a second table. I have been trying for days to figure this out but haven't found any posts or information to solve. Appreciate any guidance you all can provide.
Solved! Go to Solution.
Hi,
Using the Query Editor, split the digits into rows. See image below
Hi,
Using the Query Editor, split the digits into rows. See image below
I have tried separating but have been unsuccessful in coming up with a way to count the number of each value across 20 columns. Do you know of a way to count the instances across the newly created columns?
You have changed the question now. Share some representative data and show the expected result.
Question hasn't changed, the solution you provided was to split the column, which does not solve for the original. If I separate the column, then I would have this:
If you read the original question, I am looking for a count of the Type ID, like this:
I need to display the number of times the Type ID, 1 - 8, appears in the column(s). Hope this clarifies it for you.
Hi,
I have definitely read and understood your question clearly but you have not seen my screenshot clearly. You have to split by rows (not by columns).
My apologies. I did indeed not look closely enough. I duplicated my table, as splitting the columns into rows caused relationship issues with the original. That worked perfectly with the new table, thank you so much.
You are welcome.
Hey @EricRF
this DAX statement creates a table
Table 2 =
ADDCOLUMNS(
SELECTCOLUMNS(
GENERATESERIES( 1 , 8 , 1 )
, "Type ID" , [Value]
)
, "Count of Type ID"
, var currentTypeID = [Type ID]
return
COUNTROWS(
FILTER(
'Table'
, CONTAINSSTRING( FORMAT( 'Table'[Type ID] , "" ) , FORMAT( currentTypeID , "" ) )
)
)
)
This is the result:
This solution assumes that each number appears just once in the concatenated "Type ID" otherwise the solution will become more complex.
Hopefully, this provides what you are looking for.
Regards,
Tom
I appreciate the solution, but am not successful in implementing. First build with Power BI, so please excuse my ignorance. Also, there are not multiple instances of the same number in a row. Here's what I'm actually working with for greater context.
Table - Coaching
Field - AssignedTipsID.1
Values in the field are stored as text if that matters.
This is what I used based on your response:
This did created a new table, 'TipsCount'. This is the output I get:
Am I doing something wrong here?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |