I know that what I’m attempting to do is, probably, possible by using an SQL Query with joins but I’m trying to accomplish the following in Power BI using Direct Query using a single parameter.
I have the following tables:
TripID, StartLocationID, EndLocationID, StartDate, EndDate, VehicleNumber
(Note: LocationStart and LocationEnd are duplicated tables)
Tables are linked as follows:
LocationStart to Trip (StartLocationID > StartLocationID)
LocationEnd to Trip (EndLocationID > EndLocationID)
Parameter created for LocationName (LocationChoice) since it is what the user will know (i.e. highly unlikely that the user will know the ID). The parameter has been set for the LocationName in LocationStart and LocationEnd tables. Ultimately, I believe that it should also be set for the Trip table but, since that only contains IDs and not Names, I cannot do so. I do not wish to use merge query since I’m attempting to maintain the Direct Query storage mode for refresh purposes.
My report consists of two table displays; one displaying trips for a given day departing from LocationName, the other displaying trips arriving at LocationName.
My issue is that I must always select the respective Start or End LocationName in the filter for each report table display; otherwise, the two tables are displaying identical information. I would like to simply be able to select the parameter and have both display tables update correctly. Is there some step that I might be missing to make this work or is this just something that needs to be SQL query driven?
I am attaching a sample .pbix file that I have created to illustrate my issue. Any and all help will be greatly appreciated. Thank you!
ParamSample.pbix (101.4 KB)