get value count by seperated comma

[Anonymous]

Hi team,

I have a csv file uploaded to powerBI,  values in C_DS are comma seperated.

I want to get the value count occured in C_DS columns and create a bar or pie chart, expected result are:

Can any expert help advise?

Thanks,

Cherie

[ALLUREAN]

Hi, @Anonymous 

Is that the result you want to achieve?

https://www.dropbox.com/s/8qnmq2xx6pvu846/get%20value%20count%20by%20seperated%20comma%28allure-analytics.com%29.pbix?dl=0

Did I answer your question? Please Like and Mark my post as a solution if it solves your issue. Thanks.

Appreciate your Kudos !!!

https://allure-analytics.com/

https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA

https://www.linkedin.com/company/77757292/

[Anonymous]

@ALLUREAN  i am not able to open it successfully, powerBI reports an error 'The queries were authorized with a newer version of PowerBI Desktop.

Thanks,

Cherie

[ALLUREAN]

You need to update your Power BI Desktop version. Here is the screenshot:

[Anonymous]

@ALLUREAN , Yes, this is one of the expected chart. but as i mentioned that , I have more than one multi-value columns, and need to get the value count for each column.

For example, below is piece of data with two sample columns need to stats(C_DS and C_RT), how to deal with this? (get value count for C_DS and C_RT columns, and then create 2 bar or pie chart. One is for C_DS, another is for C_RT.)

dataCenter,C_TID,C_DS,C_RT
dcA,test_403,"performance","performanceOverallRating"
dcB,test_581,"performance","performanceOverallRating"
dcC,test_382,"performance,liveProfile","performanceOverallRating,potentialOverallRating,sysOverallPerformance,sysOverallPotential"
dcD,test_241,"performance","performanceOverallRating"
dcE,test_100,"performance","performanceOverallRating,potentialOverallRating"
dcF,test_243,"performance","performanceOverallRating"
dcH,test_282,"performance,liveProfile","performanceOverallRating,potentialOverallRating,sysOverallPerformance"
dcH,test_281,"compensation,talentFlag","finalRangePenetration,riskOfLoss"
dcK,test_1000,"compensation,performance","finalRangePenetration,objectiveOverallRating"

[Anonymous]

HI @Anonymous,

Have you tried to do unpivot on this column to convert them to attribute and value, then you can use the expression to check two condition fields to achieve your requirement.

Unpivot columns (Power Query) (microsoft.com)

Regards,

XIaoxin Sheng

[Anonymous]

@amitchandak  I know how to split it into rows now, but i meet another issue. there're two more columns contains multi-value(seperated by comma as well). when I applied your solution to all the multi-value columns(split column first and convert to rows), I found that all the stats goes wrong. 

Can you help suggest a DAX script that can get the calcuate directly without transforming data?

Thanks,
Cherie

[Anonymous]

can anyone help provide a DAX script that can calculated the count directly?

Thanks,

Cherie

[Samarth_18]

Hi @Anonymous ,

You can follow below steps to achieve this:

1. Go to edit query and select C_DS then split column as shown below:-

2. Now click on close and apply 

3.Now you can use C_DS column & value as count

Thanks,

Samarth

[Syndicate_Admin]

there @Syndicate_Admin ,

can you help translate below 2 into English? I finish split columns, but don't understand how to do bullet2 and bullet3.

*******

2. Now click close and apply

3.You can now use C_DS column and value as a count

*******

[amitchandak]

@Anonymous , On of the way split column by delimiter option in power query and split it into rows and then you can have the count at visual level

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

[Anonymous]

@amitchandak how to split it into rows?