Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
User | Count |
---|---|
120 | |
95 | |
88 | |
73 | |
66 |
User | Count |
---|---|
138 | |
112 | |
110 | |
98 | |
94 |