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.

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.

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.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.