Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
SteveMForm
Helper III
Helper III

Counting comma separated values in a column

Hi Folks

 

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

 

SteveMForm_0-1716374321830.png

SteveMForm_1-1716374577946.png

 

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

7 REPLIES 7
DataNinja777
Super User
Super User

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,

Hi @DataNinja777 sorry for delay.

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


SteveMForm_0-1717423516756.png


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

Thanks

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

 

Many thanks

Ankur04
Resolver I
Resolver I

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!

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
Resolver I
Resolver I

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

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors