Forum Discussion
Count rows by ID in table
Is it possible to display the count of rows for each specific ID in a table for each ID. So if my table has
| Color | ID |
| Blue | 1 |
| Green | 1 |
| Red | 1 |
| Yellow | 2 |
| Orange | 2 |
| Purple | 2 |
| Pink | 2 |
Can I create an measure or use a dax expression to include the count of the colors for each ID as shown below
| Color | ID | Count of Colors |
| Blue | 1 | 3 |
| Green | 1 | 3 |
| Red | 1 | 3 |
| Yellow | 2 | 4 |
| Orange | 2 | 4 |
| Purple | 2 | 4 |
| Pink | 2 | 4 |
2 Replies
Hi, TomSinAA
According to your description, you want to "create an measure or use a dax expression to include the count of the colors for each ID".
Here are teh steps you can refer to :
My test data is the same as yours.[1]If you want to add a measure on the visual , you can click "New Measure" to create :
Measure = var _id= VALUES('Table'[ID]) var _t = SELECTCOLUMNS(FILTER( ALLSELECTED('Table') ,'Table'[ID] in _id) , "Color" , [Color]) return COUNTROWS(_t)Then we can put the measure on the visual and we can meet your need:
[2] If you want to add a calculated column in the table , you can click "New Column" and enter this:
Column = var _id = [ID] var _t = SELECTCOLUMNS(FILTER('Table','Table'[ID]=_id) , "Color" , [Color]) return COUNTROWS(_t)Then we can get this result :
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
