Counting comma separated values in a column

[SteveMForm]

Hi Folks

I'm using a doughnut visual to try and show a count of occurances of a each unique value in a column:

As per above, they are also comma separated, and a value can only occur once per row.

Stuck on how to complete measure/ DAX

Thanks

[DataNinja777]

Hi @SteveMForm ,

In order to perform such data cleansing task, Power Query is your best tool in your tool box.  I suppose there are many ways to solve your problem, but what I would do in your case is to first split that column by comma delimiter to separate columns using power Query, and then unpivot the other columns which are split into different columns, so that you have a long narrow fact table which will be easily digested by power pivot engine (dax) for futher analysis.  

Best regards,

[SteveMForm]

Hi @DataNinja777 sorry for delay.

I have split the original column which had comma separated values in and get 4 new columns:

I now need to count the occurances of each value across the table- what is the best way to do this?

Thanks

[SteveMForm]

Hi @DataNinja777 @Ankur04 - any ideas on query in message 7?

Many thanks

[Ankur04]

Hi,

As I can see there are many rows where you have comma separated values. you can create new columns for each value, then try to pivot it.

another point as highlighted, each row can have only one value. so if you make a pivot each row may have more than one value. In that case you can create a logical grouping and take count of that column.

hope this may help to get some idea. please free to provide further info more better understanding.

Happy to help!

[SteveMForm]

Hi @Ankur04 

As I mentioned, I don't really wnat to keep having to pivot the table to extract values, so was looking for another wasy to get the values out.

It is not that each row can only have one value, otherwise I wouldn't have the challenge to count the distinct values and the visual would work out the box. When I mentioned unique values, it is that each unique value can only appear once in each row. It might be that several unique values apply for a row in that specific column, and thats part of the challenge.

Can you tell me more about a logical grouping

Thanks

[Ankur04]

Hi, you should first work on massagin your data. try to shape the data as per need.

[SteveMForm]

Massaging? If so, ok- but I kinda thought I wouldnt need to pivot the data every time I need to count values, and use dax instead...