Slicer Parameter Binding Using Primary Key

[Anonymous]

Hi all,

I've searched and searched but not found what I'm looking for. Maybe my terminology is wrong, or maybe I'm trying the impossible!

I'm experimenting with Power BI Desktop and have set up a direct query to my SQL database.

My goal is to use parameter binding to only return events for the selected machine within a Slicer.

This works perfectly when I set my Slicer to use the machineID field.

But in doing this the Slicer displays a list of numbers which mean nothing to the user. Id like to display the machineName field (which resides within the same table) but return the machineID as the parameter because this is what is used in the events table.

Can it be done or am I going about the problem the wrong way?

Would setting the Slicer to use machineName and return this as a Parameter be the preferred method, then modify the SQL query to use a JOIN query to get the machineID which relates to the event and other tables?

Is there another better way?

Thank you for taking the time to read,

RandomCoder.

[Anonymous]

Just an update to say that I've got this working by changing the SQL query.

There are currently two Tables, an 'Event' Table listing all the data to be reported on, and a 'Machines' Table which provides specific details for each machine and relates to the 'Event' table using a MachineID.

So to solve my problem I have set the Slicer to use the 'Machine_Name' field and applied a parameter binding to this. But to resolve this to a MachineID I've used an INNER JOIN and an ON clause as shown below...

"SELECT * FROM Event INNER JOIN Machines ON Event.MachineID = Machines.Machine_ID AND Machines.Machine_Name = '" & MachineName & "'" & " WHERE EventTime >= '" & DateTime.ToText(StartDate,"yyyy/MM/dd") & "' AND EventTime < '" & DateTime.ToText(EndDate,"yyyy/MM/dd") & "'"

I'm also filtering by a date range in the above example.

Not sure if this is the preferred way to do this, but it works and seems reasonably quick.

Hope the info helps someone else that is maybe struggling with a similar problem.

Best regards,

RandomCoder