DAX Query needs optimiziation

renanvg7 — Wed, 16 Nov 2022 00:41:56 GMT

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(%)]

)

Re: DAX Query needs optimiziation

daXtreme — Wed, 16 Nov 2022 08:03:59 GMT

Why not use Power Query to generate this table? DAX is not a mashup tool. It's an analytical tool. Power Query is for mashup.

topic Re: DAX Query needs optimiziation in DAX Commands and Tips

DAX Query needs optimiziation

Re: DAX Query needs optimiziation

Re: DAX Query needs optimiziation