Forum Discussion

crispybc's avatar
crispybc
Frequent Visitor
4 years ago
Solved

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

I have a table of  ID, Status and Timestamps. 

 

IDSTATUSTIMESTAMP
1new1/08/2022 0:33
1open1/08/2022 3:05
1hold4/08/2022 5:28
1pending12/08/2022 2:06
1open14/08/2022 23:48
1solved15/08/2022 5:57
2new1/08/2022 3:12
2hold1/08/2022 3:40
2open1/08/2022 4:10
2hold1/08/2022 4:30
2open2/08/2022 1:27
2pending5/08/2022 0:13
2solved8/08/2022 1:12
3new1/08/2022 4:16
3open1/08/2022 10:28
3solved2/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.

  • v-yadongf-msft's avatar
    v-yadongf-msft
    4 years ago

    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.

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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

  • v-yadongf-msft's avatar
    v-yadongf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.