Hello all!
I'm making an Azure Table Cube access by DAX query in import mode from a telemetry level from several clients tanks.
This is a simple table, but very heavy (thousands of clients, sometimes 15 minutes based information, and I can't ensure that all the timestamps will be equal). I only need a simple table in which has the last level from the day for each client.
So, 4 columns are available in this cube: LevelDate, LevelDateTime, DPLevel(%) and DeliveryPointId
LevelDate | LevelDateTime | DPLevel(%) | DeliveryPointId
4/11/22 | 4/11/22 23:00 | 56 | 4466 |
4/11/22 | 4/11/22 23:30 | 55 | 4466 |
5/11/22 | 5/11/22 00:15 | 54 | 4466 |
5/11/22 | 5/11/22 00:30 | 53 | 4466 |
4/11/22 | 4/11/22 23:00 | 78 | 4467 |
5/11/22 | 5/11/22 00:00 | 77 | 4467 |
5/11/22 | 5/11/22 00:15 | 76 | 4467 |
I want to generate something like this, in order to improve performance (1 value for each day for each delivery point):
LevelDate | Last Level (%) | DeliveryPointId
4/11/22 | 55 | 4466 |
5/11/22 | 53 | 4466 |
4/11/22 | 78 | 4467 |
5/11/22 | 76 | 4467 |
I've tried to create a measure in the definitions that retrieves the value for the last datetime for the context, and creating a summarizecolumns for each date and delivery point id (that's the client id), and the logic worked (see below). However, it doesn't bring any value if I try filtering for periods longer than one week, and looks like the search for the last datetime stamp is very heavy in the lastnonblankvalue measure.
Given this structure, do you see any better way to generate this table?
DEFINE
MEASURE 'DPLevel'[LastLevel(%)] = LASTNONBLANKVALUE('DPLevel'[LevelDateTime],SELECTEDVALUE('DPLevel'[DPLevel(%)]))
Evaluate
SUMMARIZECOLUMNS (
'DPLevel'[LevelDate],
'DPLevel'[DeliveryPointId],
FILTER('DPLevel','DPLevel'[LevelDate]>=DATE(2022,1,1)),
"Last Level (%)", [LastLevel(%)]
)
