Measure to calculate dot product between vector of words

[renaudstaessens]

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  

DocumentID	Word	Value
Doc1	PHP	0.01
Doc1	SQL	0.02
Doc1	MANAGER	0.05
Doc2	JAVA	0.03
Doc2	SQL	0.2
Doc2	PHP	0.7
Doc2	EXCEL	0.03
Doc2	WORD	0.03
Doc2	POWERPOINT	0.03
Doc3	PHP	0.05
Doc3	SQL	0.03
Doc3	MANAGER	0.05

Search Filter looks like this  : 

 

so that document request  representation should looks  like 

request	MANAGER	0.5
request	PHP	0.5

Cosinus similariry formula  : 

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

[Mariusz]

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

[renaudstaessens]

@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