Forum Discussion
DoctorYSG
Helper III
1 year agoDAX 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 ...
- 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
DoctorYSG
Helper III
1 year agoIt 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.
Anonymous
1 year agoNot applicable
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