Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MattGell
Advocate I
Advocate I

Next future travel reservation per person

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

4 ACCEPTED SOLUTIONS
CC182
New Member

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!

View solution in original post

Anonymous
Not applicable

@MattGell  Create a measure as shown below.


future-travel reservation.PNG

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 

 

 

View solution in original post

Anonymous
Not applicable

@MattGell  Create a measure as shown below.

future-travel reservation.PNG

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 

 

 

View solution in original post

Nailed it! Absolutely perfect.

Thank you so much

Matt

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

@MattGell  Create a measure as shown below.

future-travel reservation.PNG

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 

 

 

Anonymous
Not applicable

@MattGell  Create a measure as shown below.


future-travel reservation.PNG

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 

 

 

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

Matt

Nailed it! Absolutely perfect.

Thank you so much

Matt

amitchandak
Super User
Super User

@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])

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

CC182
New Member

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!

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors