Counting unique text occurrences across multiple columns?

[russm]

I am new to DAX and to Power BI. I’m working with an existing qualitative data set and I need to count the occurrences of unique text values across multiple columns. The existing data set used a controlled vocabulary based on a simple dictionary table.

In my data, I have several thousand records sort of like this:

Comment	Comment_Code_01	Comment_Code_02	Comment_Code_03	Comment_Code_04
I like things that fly but not things that crawl. If it has scales, I don't mind if it swims but I don’t want anything to do with it if had legs.	Likes birds	Hates bugs	Likes fish	Hates reptiles
I don’t like things that fly but I do like things that crawl.     If it has scales, I don’t mind if it has legs but I can't stand it if it swims.	Hates birds	Likes bugs	Hates fish	OK with reptiles
I don’t like anything living but I do like wood carvings of things that crawl.	Hates all creatures	Likes facsimiles of creatures
I only like birds. Well, I kind of like beetles too.	Likes birds	OK with some bugs
I hate everything living except for fish.	Only likes fish

The dictionary is about 900 records and looks something like this:

Comment_Code	Sentiment	Category
Likes Birds	Positive	Birds
Hates birds	Negative	Birds
Hates all creatures	Negative	Global
Only likes fish	Positive	Fish
Hates bugs	Negative	Insects
Likes bugs	Positive	Insects
Likes facsimiles of creatures	Positive	Nonliving
OK with some bugs	Neutral	Insects
Likes fish	Positive	Fish
Hates fish	Negative	Fish
Hates reptiles	Neutral	Reptiles
OK with reptiles	Positive	Reptiles

I need to produce a report that looks something like:

Theme	Count
Likes Birds	2
Hates birds	1
Hates all creatures	1
Only likes fish	1
Hates bugs	1
Likes bugs	1
Likes facsimiles of creatures	1
OK with some bugs	1
Likes fish	1
Hates fish	1
Hates reptiles	1
OK with reptiles	1

This was trivial in Excel using COUNTIF but I haven’t been able to work out a way to do this with DAX in Power BI. Is there a way to look at the array of Comment_Code_01: Comment_Code_04 and generate a table of all unique text values along with their frequencies of occurrence?

[ImkeF · Accepted solution]

You do this in this query editor:

1) Load your data-table

2) Check column "Comment"

3) Rightclick your mouse: "Unpivot Other Columns"

4) Check column "Value"

5) Rightclick mouse: "Group By" and just click "OK" (don't change the defaults here)

This will deliver your table below. No need for the lookuptable so far. (But you can join it if you want to show the other values as well. The operation is called "Merge" in the PBI menu)

[Varmentr1]

Do you know how to get a count based upon a concatenation of several columns without using the Group By function in Query Editor? I'm trying to do a count based upon 3 columns in a table but need to have the flexibility to show other columns associated with the data.  See my examples below for clarification.  I am very new to Power Bi and spinning my wheels on this one.

Table
Name	Race	Gender	College Graduate	High School Graduate	GED
Frank	W	M	N	Y	N
Frank	W	M	Y	Y	N
Alice	W	F	Y	Y	N
Alice	W	F	N	N	Y
John	W	M	N	Y	N
John	B	M	Y	Y	N
View if all columns are displayed
Name	Race	Gender	College Graduate	High School Graduate	GED	Count of Name, Race, Gender
Frank	W	M	N	Y	N	1
Frank	W	M	Y	Y	N	1
Alice	W	F	Y	Y	N	1
Alice	W	F	N	N	Y	1
John	W	M	N	Y	N	1
John	B	M	Y	Y	N	1
View if first three columns are displayed
Name	Race	Gender	Count of Name, Race, Gender
Frank	W	M	2
Alice	W	F	2
John	W	M	1
John	B	M	1

[russm]

Thanks, Imke!

That was just the nudge in the direction that I needed.  

My actual dataset has loads of columns on either side of the array that's similar to what I shared above.  I ended up multiselecting all of the Comment_Code_n columns and used Unpivot to flatten them. Then I related the resulting Values column with Comment_Code in the Dictionary table which gave me that counts that I needed.  Though it wasn't part of the stated problem, I was able to use a discrete row identifier to get distinct counts of unique respondents too.

Thanks again for your help!

- Russ