cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hugobdo
Regular Visitor

Approximate match for DateTime: A poor performance approach

Hello everyone!

 

I have a table ("TPSMeasurements") with a category, a datetime and the measured values I want to study (the slope distance). What I have to do is find the difference between the current value and a value "n" time before (usually 24h or 7 days) on the same category. The measurements are not regular, so I'm not able to find the exact "Epoch" to look for. Please see an example below:

 

Captura de tela 2021-05-08 024551.jpg

 

In order to find the value needed without knowing the exact date/time I've created an Index column with the following DAX:

 

Index =

var point = fTPSMeasurements[Name]

var tab =

FILTER(

fTPSMeasurements;

fTPSMeasurements[Name]=point

)

return

RANKX(

tab;

fTPSMeasurements[Epoch];;

ASC

)

 

Afterwards, I calculated how many IDs there are between the current reading and the first reading after the set "n" time:

obs.: [AnalysisTime] is a parameter the user can set, in hours

 

IDCount =

var epoch = fTPSMeasurements[Epoch]

var day = epoch-fTPSMeasurements[AnalysisTime]/24

var point = fTPSMeasurements[Name]

var LagDate =

CALCULATE(

COUNTROWS(fTPSMeasurements);

FILTER(

fTPSMeasurements;

fTPSMeasurements[Name]=point &&

fTPSMeasurements[Epoch] >= day &&

fTPSMeasurements[Epoch] < epoch

)

)

return

LagDate

 

I can finally do the following and subtract the two values:

 

SlopeDelta (mm) =

var point = RELATED(dPoints[Name])

var ind = fTPSMeasurements[Index] - fTPSMeasurements[IDCount]

var LagValue =

MAXX(

FILTER(

fTPSMeasurements;

fTPSMeasurements[Name] = point &&

fTPSMeasurements[Index] = ind

);

fTPSMeasurements[SlopeDistanceAtmosPppmCorrected]

)

var epoch =

MAXX(

FILTER(

fTPSMeasurements;

fTPSMeasurements[Name] = point &&

fTPSMeasurements[Index] = ind

);

fTPSMeasurements[Epoch]

)

var DeltaY =

IF(

ISBLANK(LagValue) || fTPSMeasurements[Index] = fTPSMeasurements[IDCount]+1; 0 ;  //Filter values on the start of the project

fTPSMeasurements[SlopeDistanceAtmosPppmCorrected]-LagValue

)

var DeltaX = fTPSMeasurements[AnalysisTime]

RETURN

DeltaY*1000  //Return value in mm

 

So far so good, it works fine on small datasets (tested on ~500k rows and worked with some loading time).

However, I now have a 1.6kk rows dataset and it is taking a very long time to load.

The IDCount column has the worst performance, by far. Is it possible to do these calculations on any other way?

 

Please let me know if I can explain it better.

Any help will be much appreciated! Thanks!

 

2 REPLIES 2
AlB
Super User III
Super User III

Hi @hugobdo 

Can you share the pbix?

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

hugobdo
Regular Visitor

Hey @AlB, thanks for your answer!

Sure! I sent it to onedrive:

https://1drv.ms/u/s!AnUEJ0hL2PrngeJKLbLkoFsQY62kCw

 

obs.: I capped the data on 50k rows and changed values, because of privacy issues

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors