topic Dynamically querying/filling a timeframe using DAX based on table of timestamps in DAX Commands and Tips

Dynamically querying/filling a timeframe using DAX based on table of timestamps

crispybc — Tue, 23 Aug 2022 13:49:00 GMT

I have a table of ID, Status and Timestamps.

ID	STATUS	TIMESTAMP
1	new	1/08/2022 0:33
1	open	1/08/2022 3:05
1	hold	4/08/2022 5:28
1	pending	12/08/2022 2:06
1	open	14/08/2022 23:48
1	solved	15/08/2022 5:57
2	new	1/08/2022 3:12
2	hold	1/08/2022 3:40
2	open	1/08/2022 4:10
2	hold	1/08/2022 4:30
2	open	2/08/2022 1:27
2	pending	5/08/2022 0:13
2	solved	8/08/2022 1:12
3	new	1/08/2022 4:16
3	open	1/08/2022 10:28
3	solved	2/08/2022 8:31

I would like to be able to dynamically select a date (based of a seperate related date table) and have it use the previous STATUS value (max TIMESTAMP<= DATE) of each ID. The final goal would be to summarize the number of IDs in each STATUS at a given point of time and to visualize this over time. (over longer time frames with resolution of days or weeks and also for smaller time frames eg. a single week at an hourly resolution)

I would need to use DAX to do this dynamically as filling values in sql or mquery would easily turn into billions of rows due to the number of ids and the resolution required.

So far everything I have tried in DAX has failed, so looking for some fresh takes as I am relatively new to DAX.

Re: Dynamically querying/filling a timeframe using DAX based on table of timestamps

amitchandak — Tue, 23 Aug 2022 16:00:03 GMT

crispybc , if this is a really big table, I would advise doing the transformation in datawarehouse, if that can be done there. getting the last status on a large table can be costly

I think you need something like this -https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

Re: Dynamically querying/filling a timeframe using DAX based on table of timestamps

v-yadongf-msft — Fri, 26 Aug 2022 10:00:31 GMT

Hi crispybc,

If you want to get the previous STATUS value, as amitchandak said, please try following DAX:

Last Status = var _max = MAXX(FILTER(ALLSELECTED('table'),'table'[ID]=MAX('table'[ID])),'table'[TIMESTAMP]) return CALCULATE(max('table'[STATUS]), filter(('table'), 'table'[TIMESTAMP] =_max))

If you want to dynamically select a date to summarize the number of IDs in each STATUS, please create a slicer based on related Date table:

The Date table I created:

If I misunderstand your demands, please provide some data screenshots (remember to hide sensitive information) to help us better solve your problem.

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.