cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Measure to calculate dot product between vector of words

The Objectives : I have a table that contains document words analysis (tf-idf) . Basically in this table for each document i have a list of words and each word has a numerical value that represents its importance in the document compare to the others.

I have also a search text field  (Smart Filter by OKVIZ) in which you can select several words (based on word column).

 

i'd like to calculate (using a measure as input in the search filter are dynamic) the cosinus similarity between my search request (vector request)  and each document (vector 1 to n)  to order the search result by relevance .

The Cosinus similarity indeed helps you to determine the "angle" between the vector request and each vector document . The closer to 1 the cosinus is the more the document match your search request. see formula after table description.

 

Table DOC ANALYSIS  

DocumentIDWordValue
Doc1PHP0.01
Doc1SQL0.02
Doc1MANAGER0.05
Doc2JAVA0.03
Doc2SQL0.2
Doc2PHP0.7
Doc2EXCEL0.03
Doc2WORD0.03
Doc2POWERPOINT0.03
Doc3PHP0.05
Doc3SQL0.03
Doc3MANAGER0.05

 

Search Filter looks like this  : 

search filter.jpg 

so that document request  representation should looks  like 

requestMANAGER0.5
requestPHP0.5

 

Cosinus similariry formula  : cosinus formula.jpg

 

So for similiarity between Request vector (call it a)  and vector Doc 1 (call it b )we have 

a1(manager) =  0.5, a2(php)  = 0.5,   b1(manager) = 0.03,  b2(php) = 0.01 b3(sql) = 0.02

 

Cos (Request and Doc1) = 0.5*0.03 + 0.5*0.01

           --------------------------------------------------

           Sqrt (0,5²+0.5²) * Sqrt(0.03² + 0.01²+ 0.02²)

 

and we have to calculate also Cos (Request and Doc2) .... then for each Cos calculated order them from the greatest to the lowest to have first the "best" matching result....

 

I really appreciate any help as i absoluletly have no idea on how to write this Cos measure .

Thanks a lot

 

2 REPLIES 2
Super User III
Super User III

Hi @renaudstaessens 

 

Try OCS DAX function

https://docs.microsoft.com/en-us/dax/cos-function-dax

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

@Mariusz Hi mariuusz the issue is not the standard cos function in DAX the problem is how to implement the function i gave the formula using measure in DAX with that data table structure ...i tried for 2 days now to find a solution but really stuck

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors