topic Re: Counting comma separated values in a column in DAX Commands and Tips

Counting comma separated values in a column

SteveMForm — Wed, 22 May 2024 10:43:12 GMT

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

Re: Counting comma separated values in a column

Ankur04 — Wed, 22 May 2024 11:25:03 GMT

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

Re: Counting comma separated values in a column

SteveMForm — Wed, 22 May 2024 11:49:33 GMT

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...

Re: Counting comma separated values in a column

Ankur04 — Wed, 22 May 2024 12:29:07 GMT

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!

Re: Counting comma separated values in a column

SteveMForm — Thu, 23 May 2024 09:32:21 GMT

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

Re: Counting comma separated values in a column

DataNinja777 — Sun, 26 May 2024 07:57:58 GMT

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,

Re: Counting comma separated values in a column

SteveMForm — Mon, 03 Jun 2024 14:06:15 GMT

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

Re: Counting comma separated values in a column

SteveMForm — Wed, 05 Jun 2024 09:23:33 GMT

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

Many thanks