topic Re: Next future travel reservation per person in DAX Commands and Tips

Next future travel reservation per person

MattGell — Tue, 28 Jul 2020 21:48:08 GMT

Hello,

can anybody please help with something I know is simple but I'm fairly new to DAX and I'm not finding it very intuitive.

I have 2 tables table1 called "AVI_USER" with people with unique "UserID" as primary key. Table 2 is for reservations for those people called "AVI_RES_PAX". the start dates of the reservations are stored in "AVI_RES_PAX[REQ_FROM_DT]". Each person could have many reservations into the future but I just want the next one (>TODAY) for each transport mode.

Reservations can be for different modes of transport as distinguished in "AVI_RES_PAX[REQ_MODE]". (Fixed Wing or Rotary Wing)

I simply want columns for the next reservation date for each mode per person and columns to show the origin and destination.

Desired result below:

UserID NextFixedWingDate Origin Destination NextRotaryWingDate Origin Destination

NM001 01-Sep-20 LHR GIG 31-Aug-20 FTS ABZ

NM002 10-Oct-20 CDG HOU 01-Nov-20 PPA ABZ

NM002 20-Aug-20 LOS JOB

I know it looks simple but I just cant get it

Please help

Matt

Re: Next future travel reservation per person

CC182 — Wed, 29 Jul 2020 01:37:47 GMT

Hi,

1. Create a new column in Table 2: "Future Date" = if([Reservation start date] - TODAY()>0,"Y","N")

2. Create a Matrix visualization as follows:

- Rows: UserID

- Columns: Mode

- Values: 'Earliest' Reservation start date --> 'First' Origin --> First 'Destination

- FIlters: (i) Future Date = Y; (ii) Top 1 UserID by Earliest Reservation start date

Hope that was what you were looking for!

Re: Next future travel reservation per person

amitchandak — Wed, 29 Jul 2020 02:38:20 GMT

MattGell , Try new columns like

next date = =minx(filter(table,table[UserID] = earlier([UserID]) && [NextFixedWingDate] >earlier([NextFixedWingDate])),[NextFixedWingDate])
next Origin = =minx(filter(table,table[UserID] = earlier([UserID]) && [NextFixedWingDate] =earlier([next date])),[Origin])
next Destination = =minx(filter(table,table[UserID] = earlier([UserID]) && [NextFixedWingDate] =earlier([next date])),[Destination])

Re: Next future travel reservation per person

Anonymous — Wed, 29 Jul 2020 05:05:51 GMT

MattGell Create a measure as shown below.

The measure above shows how to calculate date for a user having fixed wing mode. You can alter at line 8 and create a new measure for other mode.

Similarly you can create measure for calculating Origin and Destination by tweaking at line 8 and 10.

To test this go to report pane and choose table visual, drag AVI_USER[UserId] column and this measure.

Hope this helps you. If you find this helpful and working please mark it as solution.

Thanks

Re: Next future travel reservation per person

Anonymous — Wed, 29 Jul 2020 05:01:35 GMT

MattGell Create a measure as shown below.

The measure above shows how to calculate date for a user having fixed wing mode. You can alter at line 8 and create a new measure for other mode.

Similarly you can create measure for calculating Origin and Destination by tweaking at line 8 and 10.

Hope this helps you. If you find this helpful and working please mark it as solution.

Thanks

Re: Next future travel reservation per person

MattGell — Wed, 29 Jul 2020 13:04:37 GMT

Thats what I needed but not as a stand alone matrix - need those columns in another table, but its an awesome alternative that I will use in other reports.

Thank you so much.

Re: Next future travel reservation per person

MattGell — Wed, 29 Jul 2020 14:42:39 GMT

Nailed it! Absolutely perfect.

Thank you so much

Matt

Re: Next future travel reservation per person

MattGell — Wed, 29 Jul 2020 14:45:18 GMT

This is EXACTLY what I was looking for! works perfectly. Thank you so much

Matt

Re: Next future travel reservation per person

MattGell — Wed, 29 Jul 2020 14:46:59 GMT

This helped a lot thank you, but I preferred using a measure that Sparse Coder suggested.