How to convert a comma delimited text field in one column and count each text field across rows

[mde]

I have a feedback table that has the following columns:

location (e.g. New York)

rating (1 to 5)

type (e.g. employee interaction)

subtype (e.g. clinical)

employee id (e.g. 324)

reasons (comma delimited text of reasons e.g. Communication, Knowledge, Understanding of my situation, Respect, Other)

I want to create a measure for each reason how many times it is present in the table for each location, by rating, and by type/subtype/employees

In excel I am creating this table (and plotting a bar chart with reasons on left and absolute count or percent of occurrences of the reason) by using countif function that matches on all the variables I'm filtering by. I'm new to PowerBI and DAX and am not sure how to accomplish the same.

Thinking I may want to first create a new calculated table with all the reasons text fields after splitting them from the reasons column in my feedback table but don't know how to do that either.

Any help is much appreciated!

Any help would be much appreciated

[Ashish_Mathur · Accepted solution]

Hi,

You need to create one row per reason.  You may download the PBI file from here.  Click on the Data icon on the left hand side pane.

[Rfranca]

hi, @mde

Could you provide the example file to help?

[mde]

Here is the main file which contains a number of columns with one column having a comma delimited string of text fields https://drive.google.com/file/d/1-0uIowwrsPdzQdHTba7XhA07PPe1h2P0/view?usp=sharing

I created another table for the text fields called "reasons" (see link below) as that is how I do it excel where I countif based on the matches with the text field and other variables in the main table (e.g. rating, location, etc.)

https://drive.google.com/file/d/1-2oT7hDLhjnIbZUpkp7ngcR4ESp-tREx/view?usp=sharing

Thanks for your help!

[Lind25 · Accepted solution]

Hi
In query editor you need to split your reasons in to rows. Use the split column function and under advanced choose rows. You will most likely need to use the trim function to remove trailing spaces.

You can then relate your main table to your reasons table and create a count measure to get your answers.

[mde]

Thanks! This solved it. I created an index (as I don't have an id) in my original table and then duplicated it with just id and reasons to split them by delimiter and expand into rows. I could then create the bar charts easily.

[Ashish_Mathur · Accepted solution]

Hi,

You need to create one row per reason.  You may download the PBI file from here.  Click on the Data icon on the left hand side pane.

[nickchobotar]

@mde

Could you please post the finished report. The end result.

Thanks, N -