Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Try OCS DAX function
https://docs.microsoft.com/en-us/dax/cos-function-dax
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |