Optimization of the measure coding

[reynold522]

We have more than 50 IoT devices.

each IoT device provide voltage for every 4millisecond, we calcualte deep discharge with voltage<1.8V.

I did following measure with formula writen:

Deep Discharge hours = Calculate(COUNT(Table[BATTVOLTAGE]), Filter(table,table[BATTVOLTAGE]<=1.8))/900

*******/900 as sample rate 4 ms********

I put this calculated value in a table view:

coloum 1: IoT device Number

coloum 2: Deep Discharge hours

it works when I filter the duration to the latest day

It wont work when i filter the duration to latest 2+ days with hint: the resulteset of   a query to external data source has exceeded the maximum allowed size of 1000000 rows.

is there a way to optimized the code or do it in some other ways so that I can have longer period evaluation than just one day?

Thanks in advance!

Best regards

[Greg_Deckler · Accepted solution]

@reynold522 Sounds like this is DirectQuery, yes? In that case, I suggest you implement an Aggregation table if you have not already. User-defined aggregations - Power BI | Microsoft Learn