Forum Discussion
How to count different values in same cell?
I have SharePoint -list as a Source where a have a choice column where multiple choices are allowed, let say possible choices are A, B, C, D.
Example Cells' Content:
B
C
A,C
C,D
A,B,C
A
I have the data already in Power BI. The limiter is comma in the cell.
How can I count ALL the different values in the column?
So Count A would be 3,
B would be 2,
C would be 4
D would be D.
Hi,@Finnair
You can try the following methods.
A = IF ( SEARCH ( "A", [Column], 1, 0 ) <> 0, 1, BLANK () )B = IF(SEARCH("B",[Column],1,0)<>0,1,BLANK())C = IF(SEARCH("C",[Column],1,0),1,BLANK())Measure:
Count A = COUNT('Table'[A])Count B = COUNT('Table'[B])Count C = COUNT('Table'[C])Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more.
3 Replies
Anonymous , One of the options is to split column by delimiter into rows
https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/
If you want a Power Query solution:
Split the columns by , (comma)
Add an Index column.
Select the Index column and 'Unpivot Other columns'.
Select the Value column, choose Group By (it should default to Counting the rows so click OK).
All done with the interface
Hi,@Finnair
You can try the following methods.
A = IF ( SEARCH ( "A", [Column], 1, 0 ) <> 0, 1, BLANK () )B = IF(SEARCH("B",[Column],1,0)<>0,1,BLANK())C = IF(SEARCH("C",[Column],1,0),1,BLANK())Measure:
Count A = COUNT('Table'[A])Count B = COUNT('Table'[B])Count C = COUNT('Table'[C])Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more.
