Forum Discussion
Keyword Prevalence Metric
I'm working with a workflow where there's a Product Description text field. I'd like to create a visual that shows the count of certain keywords used in the Product Description field.
for example, the records may show "bar nut", "bar spacer", "spacer rod", and "bracket". The graph for this data would then show the count for each keyword we've identified, such as,:
bar = 2
nut = 1
spacer = 2
rod = 1
bracket = 1
For my first attempt, I created a new table with all Keywords. I put the Keywords in the Axis field of the visual and Product Description in the Values field. At a glance, this is what I'm after, but further examination made me realize this only shows where the Keyword = Product Deacription rather than the count of records that contains the keyword. Using the previous example, the only thing showing in the visual would be:
bracket = 1
because it's the only exact match.
Can I make this work to show where the keywords show up as part of a longer Description or is there a better way to do this?
4 Replies
MikeJohnsonZAResponsive Resident
Hi
The correct approach in Power BI is usually to build a model that supports the type of analytics that you are looking to do and this should almost always take the form of a star schema. To do this you could duplicate the product title into a separate table then split the text by delimiter using a "space" then splitting the values by row.
However, it is still possible to get the result you are looking for, you could use something like the formula below.
Keyword count =
VAR _KeyWord =
MAX ( 'Key Words'[Key Word] )
RETURN
IF (
HASONEVALUE ( 'Key Words'[Key Word] ),
SUMX (
VALUES ( 'Product Titles'[Products] ),
IF (
SEARCH (
_KeyWord,
'Product Titles'[Products],
1,
BLANK ()
)
= BLANK (),
BLANK (),
1
)
),
BLANK ()
)The data used will update constantly and the dashboards aren't meant to be manipulated at anytime after creation. Is one of these options preferred based on that additional info?
- MikeJohnsonZA
Neither would require you to edit the PBIX afterwards, if you are going to be doing a lot of this analytics then it would be worth going for the first option as you would be able to do more than just your keyword frequency analysis, it would also perform better, at the expense of your model getting slightly bigger.
If the keyword frequency analysis is all you need then you should be fine with the DAX measure only.
I have the star schema setup and I've created another table. I don't think I'm understanding how to make the new table, with delimited columns, work since the keywords are repeated in most columns.
