How to summarize/count the frequency of keywords from 3 different columns?

[Dom123]

Ok, this seemed so simple to do but it doesn't seem as simple to implement. 🙂

How to visualize the overall count of each keyword when they are coming from 3 different columns?

Simplified example of the table (only showing data to be aggregated, other columns not shown):

Name	Key1	Key2	Key3
Item1	A	C	null
Item2	A	null	null
Item3	C	B	C
Item4	D	D	null
Item5	D	E	D
Item6	E	null	null

Explanation:

• I have a list of items with associated keywords
  • The table/list has many other columns so "unpivot" is not feasible.
  • Each item can have up to 3 keywords (each in a different column named Key1, Key2, Key3).
  • Each item has at least 1 keyword (in Key1).
  • When an item has only 1 or 2 keywords, there is "null" value in the 2nd or 3rd column respectively.
  • Not all types of keywords necessarily appear in column Key1 so it cannot be used as a "reference".
  • Keywords are not necessarily known so a specific text search is not possible.

The desired results should be:

Keyword	Frequency
A	2
B	1
C	3
D	4
E	2

What I tried and didn't work:

• I tried creating a new table of "unique" keywords from the three columns with functions DISTINCT, UNION, and FILTER but I end up with a table that can only have a relationship with one column so the calculations are all wrong when adding the 2nd and 3rd column since it uses the first column as reference but that first column does not necessarily includes all possible keywords.
• I tried creating a new table with SELECTCOLUMNS to have only the above information but I still cannot unpivot because this function is only available in Power Query (which refers to the original source data and not that new table).

Any information would be greatly appreciated.

Thanks!

[Ashish_Mathur]

Hi,

Why is unpivot not feasible?  Select all columns other than the 3 key columns, right click and select "Unpivot Other Columns".

[Dom123]

Thanks but that is messing up all my table (which contains 30 columns of data).

All items/records then becomes duplicates (even triplicates) and that is interfering with all the other types of analysis I am doing.

[vicky_ · Accepted solution]

I don't think this is the most performant solution, but I hope it helps:

First I used 

keywords = DISTINCT(
    UNION(
        DISTINCT('Table (3)'[Key1  ]), 
        DISTINCT('Table (3)'[Key2  ]), 
        DISTINCT('Table (3)'[Key3])
    )
)

to create a new table with all of the unique keywords.

Then I used a DAX measure: 

frequency = 
var allKeys = UNION(
    SELECTCOLUMNS('Table (3)', "key", 'Table (3)'[Key1  ]),
    SELECTCOLUMNS('Table (3)', "key", 'Table (3)'[Key2  ]),
    SELECTCOLUMNS('Table (3)', "key", 'Table (3)'[Key3])
)
var frequencies = COUNTROWS(FILTER(allKeys, [key] = SELECTEDVALUE(keywords[Key1  ])))
return frequencies

to just get a list of all the keys, and then count them if they matched the value in the unique keywords from the table above. 

And as you can see, I can get the frequency. I'm sure you can add a filter to remove the null row if that's something that shouldn't appear. 

[Dom123]

Thank you @vicky_ !

It works perfectly! 👍

I didn't realize you could actually do that with UNION. When I tried it directly (without a variable), it was removing duplicates (which I wanted to keep of course).

FYI, I didn't have the "null" problem because when I created my "unique" list of keywords, I already filtered out null/blank values like this:

keywords = DISTINCT(
    UNION(
        DISTINCT(FILTER(VALUES(Table[Key1],Table[Key1]<>BLANK())), 
        DISTINCT(FILTER(VALUES(Table[Key2],Table[Key2]<>BLANK())), 
        DISTINCT(FILTER(VALUES(Table[Key3]),Table[Key3]<>BLANK()))
    )
)

Thank you again, much appreciated!

Dom

[vicky_]

Nice! I'm happy to hear that worked for you.