Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |