Parameter Confusion

Power Query
1

Hi Everyone,

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:

LocationStart:

StartLocationID, StartLocationName

LocationEnd:

EndLocationID, EndLocationName

Trip:

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)

2

i will like to be certain that i understand from the data table attached in pbix - are you saying if you select say 5/14 - the top table should show 2 rows - vehicle 880 and 890 and the bottom table should show all 5 records?

image
image740×242 5.63 KB

3

Hi,

Actually, what I’m really after is the location for a certain date; so, for instance, if the parameter is set to MA (LocationID = 2), the first record should display in the top table and the third record should show in the bottom table.

4

Hello - so like so many things in DAX it all comes down to data modeling.
what i did was break your trip table in 2 table using summarize function of DAX

StartTable = SUMMARIZE(Trip, Trip[StartDate], Trip[StartLocationID], Trip[TripID], Trip[VehicleNumber])
EndTable = SUMMARIZE(Trip, Trip[EndDate], Trip[EndLocationID], Trip[TripID], Trip[VehicleNumber])

then i created a location master table… i just picked up some states at random for my modeling exercise that matched location id in your trip table

I also added a date table for future expansion

The trip table can now be removed from the model since all the critical elements are part of start and end table

attaching pbix file for your reference. refer data tab… the start table and end table are at the top and bottom … there is a slicer on location.

ParamSample.pbix (414.2 KB)

image
image726×756 9.75 KB

pls let me know if the solution worked for you

5

@C9411010 Thank you so much for your help. This did, indeed, get me closer to my goal. Instead of using your illustrated DAX method, I duplicated the Trip table in Power Query Editor and adjusted/modified the separate tables there. I’m not sure if that’s a best practice or not but it did seem to reduce the report file size greatly for some reason.
My only remaining question now: is there any way to get around using a slicer? Ultimately, I’d like to be able to select the parameter (Location [Name]) and then just have the tables populate accordingly, without the extra step of having to, also, select the Name in the slicer.
Thanks again!

6

can you pls expand on this a little bit?

Ultimately, I’d like to be able to select the parameter (Location [Name]) and then just have the tables populate accordingly,?

7

Sure; in the solution you’d provided, the parameter (LocationChoice) is not incorporated into the model. My original question concerned connecting the LocationChoice parameter, which is text, to a table only having the corresponding LocationID. When I configure your solution with the parameter in Locations[Name], the report appears to break; either everything shows up in the report tables or nothing, but not the expected filtered results.
What I would, ultimately, like to happen is this:

  • With Parameter selection of NY:

  • Top report table shows Trips departing from NY for specified date

  • Bottom report table shows Trips arriving at NY for specified date

  • Both tables display start and end locations

When Parameter is changed to MA:

  • Top report table shows Trips departing from MA for specified date
  • Bottom report table shows Trips arriving at MA for specified date
  • Both tables display start and end locations