Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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!
@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
@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
@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
@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
This is EXACTLY what I was looking for! works perfectly. Thank you so much
Matt
Nailed it! Absolutely perfect.
Thank you so much
Matt
@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.
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.
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |