HELP WITH DAX CODE

Question

Hi everyoneim new to PowerBI. Here is my request from clients.&nbsp;A Fact table contains keywords such as healthy toothpaste, christmas healthy toothpaste, yellow toothpaste,... etc. And each keyword has a same metrics such as orders, units throughout a year like this&nbsp;KEYWORDS&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Units&nbsp; &nbsp; &nbsp; &nbsp; weekhealthy toothpaste&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1toothpaste healthy male&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2christmas healthy toothpaste&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3yellow toothpaste&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4healthy toothpaste&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2And i have a DIM table that contains single root of keywords like thissingle roothealthychristmasyellow&nbsp;toothpasteBy choosing multiple single roots, the table will illustrate the metrics of all keywords that contain all the selected root no matter of the position of root in keywords.&nbsp;For example: by choose healthy , toothpaste root, the results will be like this&nbsp;healthy toothpaste&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1toothpaste healthy male&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2christmas healthy toothpaste&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3healthy toothpaste&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2Any solutions to do this in Power BI? Thanks all

samwiseowl · Accepted Answer

Hi&nbsp;no123&nbsp;Something like this?&nbsp;I used a measure in the Filters on this visual section:Contains word = var curr = SELECTEDVALUE('Key'[Keywords])&nbsp; --Current row in the tablevar countWords = COUNTROWS(values('root'[single root])) --words ticked in slicervar filtwords =COUNTROWS(&nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; 'root' --filter slicer table&nbsp; &nbsp; &nbsp; &nbsp; ,CONTAINSSTRING(curr,[single root]) --check if current word found in row&nbsp; &nbsp; )) --count how many words matched the rowRETURNif(&nbsp; &nbsp; filtwords = countWords --if the number of words in row match words ticked&nbsp; &nbsp; ,1 --return 1&nbsp; &nbsp; ,0 --others)

Forum Discussion

HELP WITH DAX CODE

2 Replies

Related content

Missing Codes

User Group Leader Code of Conduct

DAX code issue

Error Code: 9518

Dax code optimization