topic Re: Selecting the most recent row of data based on date selection in DAX Commands and Tips

Selecting the most recent row of data based on date selection

MarDen94 — Fri, 02 Sep 2022 07:02:32 GMT

Hi,

My dataset is set up as follows:

A table with status changes for tickets where each status change is one row of data with a unique ID, the tickets can be recognized by a caseID. See also below:

The goal for me would be to select a value in the most recent row of data based on a dynamic (singular) date selection.

So if the selected date would be 01-03-2022 then a value form first row of data would show for this specific CaseID.

I was able to to this with a MAXX statement:

Selected Status = VAR SelectedCalendarDate = SELECTEDVALUE('Calendar'[CalendarDate]) VAR SelectedCase = SELECTEDVALUE(Case_history[caseid]) VAR SelectedHistoryDate = MAXX( FILTER( ALL(Case_history), Case_history[createddate] < SelectedCalendarDate && Case_history[caseid] = SelectedCase ), Case_history[createddate]) VAR SelectedStatus = MAXX( FILTER( ALL(Case_history), Case_history[createddate] = SelectedHistoryDate && Case_history[caseid] = SelectedCase), Case_history[newvalue]) RETURN SelectedStatus

The thing is when aggregating this data for a larger number of tickets the data takes very long to load.

Are there things that I can do to speed things up?

NB. A way of calcaulting where the result is only aggregated data is also acceptable.

Re: Selecting the most recent row of data based on date selection

amitchandak — Fri, 02 Sep 2022 08:06:38 GMT

MarDen94 , Refer if my blog on a similar topic can help

https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee884e

Re: Selecting the most recent row of data based on date selection

MarDen94 — Fri, 02 Sep 2022 11:07:02 GMT

Hi,

Thank you for your suggestion. But I cannot see how I would add a filter based on date to this calculation.

I really want to be able to select a date and then get the calculation based on that selected date.

Preferably the date selection is taking place from a different calendar table.

Re: Selecting the most recent row of data based on date selection

colacan — Sat, 03 Sep 2022 11:37:45 GMT

MarDen94 Hi Marden94,

In your measure, there is a nested iterator "MAXX - FILTER(ALL()..." which requires lots of time and resources when data is big.

Try not to filter the whole table (FILTER(column1, column2..) or use other table functions- like CALCULATETABLE rather than FILTER.

For your measure, by using calculate you can get the same value as below;

Status2 =

VAR SelectedCalendarDate = SELECTEDVALUE('Calendar'[date])

VAR SelectedCase = SELECTEDVALUE(Case_history[caseid])

VAR SelectedHistoryDate =

CALCULATE(max(Case_history[createddate]),

Case_history[createddate] < SelectedCalendarDate && Case_history[caseid] = SelectedCase )

VAR SelectedStatus =

CALCULATE(MAX(Case_history[newvalue]),

Case_history[createddate] = SelectedHistoryDate && Case_history[caseid] = SelectedCase)

RETURN SelectedStatus

Hope this helps you.
- please mark this post as solution if this post helped. appretiate kudo.

Re: Selecting the most recent row of data based on date selection

tamerj1 — Sat, 03 Sep 2022 12:02:22 GMT

Hi MarDen94
Actually filtering ALL ( Table ) is not required but you're doing that twice.

The filter context already contains the table of each CaseID and no need to filter ALL ( Table ) for the current CaseID. Much more simple formula as follows

Selected Status = VAR SelectedCalendarDate = SELECTEDVALUE ( 'Calendar'[CalendarDate] ) VAR TableBefore = FILTER ( Case_history, Case_history[createddate] < SelectedCalendarDate ) VAR PreviousRecord = TOPN ( 1, TableBefore, Case_history[createddate] ) VAR SelectedStatus = MAXX ( PreviousRecord, Case_history[newvalue] ) RETURN SelectedStatus

Re: Selecting the most recent row of data based on date selection

MarDen94 — Mon, 05 Sep 2022 12:40:37 GMT

Hi,
thanks for the reply.

When using this it gives me the correct status on the selected date. But when I try to use the created 'status2' measure to count the number of cases in a specific status on a historical date it shows all the cases which had that status (eg. "New") before the selected date.

Would there be an easy way to add this calculation to the measure OR create a seprate measure to that end?

Thanks