Forum Discussion
Helper IIIDAX for comparing time series data
Here is my rolling Pearsons Correlation Coefficient solution:
CorrTable = ADDCOLUMNS( SUMMARIZE( 'Traces', 'Traces'[CorrID], 'Traces'[DateStamp] ), "OutlookLatency", CALCULATE( AVERAGE('Traces'[ResponseTime]), 'Traces'[AppName] = "Microsoft Outlook" ), "TokenLatency", CALCULATE( AVERAGE('Traces'[ResponseTime]), 'Traces'[AppName] = "AAD Token Broker Plugin" ), "CredentialLatency", CALCULATE( AVERAGE('Traces'[ResponseTime]), 'Traces'[AppName] = "Credential Manager UI Host (Windows)" ), "EntraLatency", CALCULATE( AVERAGE('Traces'[ResponseTime]), 'Traces'[AppName] = "Microsoft Entra" ) ) TokenCorr = VAR cTable = FILTER( ADDCOLUMNS( VALUES('CorrTable'[CorrID]), "X", CALCULATE(AVERAGE('CorrTable'[OutlookLatency])), "Y", CALCULATE(AVERAGE('CorrTable'[TokenLatency])) ), AND( NOT (ISBLANK([X])), NOT (ISBLANK([Y])) ) ) VAR Count_Items = COUNTROWS(cTable) VAR Sum_X = SUMX(cTable,[X]) VAR Sum_X2 = SUMX(cTable,[X] ^ 2) VAR Sum_Y = SUMX(cTable,[Y]) VAR Sum_Y2 =SUMX(cTable,[Y] ^ 2) VAR Sum_XY =SUMX(cTable,[X] * [Y]) VAR Pearson_Numerator = Count_Items * Sum_XY - Sum_X * Sum_Y VAR Pearson_Denominator_X = Count_Items * Sum_X2 - Sum_X ^ 2 VAR Pearson_Denominator_Y = Count_Items * Sum_Y2 - Sum_Y ^ 2 VAR Pearson_Denominator = SQRT(Pearson_Denominator_X * Pearson_Denominator_Y) VAR TokenCorr = DIVIDE( Pearson_Numerator, Pearson_Denominator ) RETURN TokenCorr
Helper IIIthank you Madadi,
I searched in the DAX documentation for Spearmen correlation, but I don't see any function for it. DAX function reference - DAX | Microsoft Learn nor a special Cosine correlation. There are some articles in the user forums. I did see CoPilot suggest the below code, but I don't think that is really the correct mathematical way to tackle this. (we don't have sine waves here).
what would be best is a cross-correlation function, but that is a good deal of coding to ge there, and DAX is not really well setup for this:
Cross-correlation - Wikipedia
```
The cosine correlation (or cosine similarity) is a measure used to determine the similarity between two vectors in a multi-dimensional space. It is widely used in fields like machine learning, natural language processing, and recommendation systems. The formula for cosine similarity is:
$$\text{Cosine Similarity} = \frac{\mathbf{A} \cdot \mathbf{B}}{|\mathbf{A}| |\mathbf{B}|}$$
Where:
- $$\mathbf{A} \cdot \mathbf{B}$$ is the dot product of vectors $$\mathbf{A}$$ and $$\mathbf{B}$$.
- $$|\mathbf{A}|$$ and $$|\mathbf{B}|$$ are the magnitudes (Euclidean norms) of the vectors.
In DAX (Data Analysis Expressions), commonly used in Power BI or Excel, you can calculate cosine similarity between two vectors (e.g., columns of data) by following these steps:
Example DAX Calculation
Assume you have two columns, ColumnA and ColumnB, in a table called DataTable.
Dot Product:
Copy the codeDotProduct = SUMX(DataTable, DataTable[ColumnA] * DataTable[ColumnB])Magnitude of Each Vector:
Copy the codeMagnitudeA = SQRT(SUMX(DataTable, DataTable[ColumnA] * DataTable[ColumnA])) MagnitudeB = SQRT(SUMX(DataTable, DataTable[ColumnB] * DataTable[ColumnB]))Cosine Similarity:
Copy the codeCosineSimilarity = DIVIDE([DotProduct], [MagnitudeA] * [MagnitudeB], 0)
Notes:
- The DIVIDE function ensures safe division, avoiding errors if the denominator is zero.
- This approach assumes the vectors are represented as rows in the table.
If you need further clarification or help implementing this, feel free to ask! 😊
```
Hi DoctorYSG ,
Thanks for reaching out to the Microsoft fabric community forum.
If DAX doesn't meet your data transformation needs, consider using a Fabric Notebook with PySpark. Notebooks offer an environment where you can process, filter, and reshape data before visualizing it in Power BI. This is useful when you're working with logic or operations that are difficult to implement in DAX.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thank you
- DoctorYSG
Right now, I am in the GovCloud version of PowerBI (FedRamp aproval of Fabric is still in the process). But I tend to agree with you. What I am probably looking for is rolling correlations, or cross correlations which is available in Pandas for Python. I am going to leave this open so that others who have tried to do rolling correlations in DAX can reply (and there are some use cases where delaying the processing till DAX makes sense).