Forum Discussion
Helper IINew Table with values from another table from different columns and rows
Hi Guys,
I have a table with many columns, with main data for different Buses , departure / arrival dates and cities. The last 4 columns are calculated date/time of the First (earliest) departure, first (earliest ) arrival , last (latest ) departure and last (latest) arrival for the particular date (per bus). Since one bus performs several trips per day - I have multiple rose per one Bus per day.
I need to create new table (calculate ) where to list just in one row the general information (bus, arrival date, city) and First (earliest) departure, first (earliest ) arrival , last (latest ) departure and last (latest) arrival - all this in one row per bus / date.
see attached excel file example here. Sheet 1 is the original table and sheet 2 is the new table I want to create.
This should be done via DAX.
many help / ideas - thanks 🙂
kmilarov add a new table using the following DAX expression, tweak it as you see fit.
Bus New = SUMMARIZE ( Bus, Bus[Arr. Date], Bus[Arr.City], Bus[Bus ], "First Dep.First Dep", MAX ( Bus[First Dep.First Dep] ), "Last Arr.Last Arr", MAX ( Bus[Last Arr.Last Arr] ), "Last Dep.Last Dep", MAX ( Bus[Last Dep.Last Dep] ), "First Arr.First Arr", MAX ( Bus[First Arr.First Arr] ) )
1 Reply
- parry2k
Super User
kmilarov add a new table using the following DAX expression, tweak it as you see fit.
Bus New = SUMMARIZE ( Bus, Bus[Arr. Date], Bus[Arr.City], Bus[Bus ], "First Dep.First Dep", MAX ( Bus[First Dep.First Dep] ), "Last Arr.Last Arr", MAX ( Bus[Last Arr.Last Arr] ), "Last Dep.Last Dep", MAX ( Bus[Last Dep.Last Dep] ), "First Arr.First Arr", MAX ( Bus[First Arr.First Arr] ) )