Forum Discussion
DAX for comparing time series data
I have seen some DAX for doing Pearson's Correlation, but it is not really designed for comparing two time series.
What would you suggest for two columns (same table) with values that look highly correlated (I want a single metric as output).
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
13 Replies
Averages are not good. One needs to see if one curve tracks the peaks and valleys of the other in a scale free manner.
Hi DoctorYSG ,
Thanks for reaching out to the Microsoft fabric community forum.
I came across a few correlation metrics that I believe are well suited for your needs.
Please try out Cosine similarity and spearman correlation, and let us know if they help in checking correlation of your data.
If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thank youthank 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
It cannot be an aggregate over time. In narritive form the question is: At the point in time (approximately) when there is a Outlook wait event, what is the related token wait (on it's timeline). That is we are doing sliding windows on both timelines and comparing.
Hi DoctorYSG ,
Thank you for providing additional context to your scenario, but as you have mentioned there's a significant amount of coding with DAX, and it is not ideally set up for this scenario.
So as an alternative please try to utilize Python/R visual in Power BI and try to create visualization using advanced statistics
Create Power BI visuals using Python in Power BI Desktop - Power BI | Microsoft Learn
Thank you
Hi DoctorYSG ,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
