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 Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors