Forum Discussion
DAX for comparing time series data
- 1 year ago
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
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
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).
- Anonymous1 year agoNot applicable
Hi DoctorYSG ,
Accepting the helpful reply as solution may help some community members in the future who may come across this post.
You could always reopen this thread or create a new one later.Thank you.
- DoctorYSG1 year ago
Helper III
I will post my rolling correlations coeficients when it is done.
- Anonymous1 year agoNot applicable
Hi DoctorYSG ,
Thank you for the update, we are going to keep this thread open as per your request.For any further discussions or questions, please post in this post or create a new post in the Microsoft Fabric Community Forum, we’ll be happy to assist.
Thank you for being part of the Microsoft Fabric Community.