Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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:
The desired results should be:
Keyword | Frequency |
A | 2 |
B | 1 |
C | 3 |
D | 4 |
E | 2 |
What I tried and didn't work:
Any information would be greatly appreciated.
Thanks!
Solved! Go to 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.
Hi,
Why is unpivot not feasible? Select all columns other than the 3 key columns, right click and select "Unpivot Other Columns".
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.
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.
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
Nice! I'm happy to hear that worked for you.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
146 | |
72 | |
63 | |
52 | |
51 |
User | Count |
---|---|
208 | |
91 | |
62 | |
59 | |
56 |