table with id(int), and name(string), slicer shows names, how to bind param to corresponding id?

[Anonymous]

I have:

1. A query that returns a table with two columns: ItemId (int), and ItemName (string). Table contains multiple rows.

2. A Slicer showing Items' names, for users to select one or more items by name from the list.

3. A PowerBI parameter bound to the ItemName (string)

I use that parameter to query SQL Server (Direct Query), using the names selected by the user in the slicer in my SQL query.

And that works 'ok.'

However, what I would really like to do is pass the Item IDs (INT) of the items selected by the user in the slicer, instead of Item Names. 

Would that be doable? How?

I have tried many things but nothing worked.

For instance, tried, 

   let ItemId = MyTable[ItemId]{List.PositionOf(MyTable[ItemName], ItemNameParameter, 0)},

...and then I tried using that ItemId in my query,

 Source = Sql.Database(SqlServerParameter, SqlDatabaseParameter, [Query=FilterQuery])

But PowerBI's Firewall complianed with an error like:

 "Formula.Firewall: Query 'Blah' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."

and I could not figure out how to resolve that.

Alternatively: is there any way for a query to reference a parameter bound to another query? 

Thanks,

Claudio

[vojtechsima]

Hello, @Anonymous 

should be fairly easy.

See screenshot here, I am slicing through "Product" which is complete nonsense but then actually filtering by associated ID.

This is my slicer table:

If you have trouble with Privacy levels, either align them, so they are the same or ignore them:

or in settings like this:

Then the actually query look like this:

 selectedItem = Table.SelectRows(listForParam, each item = [Product]){0}[id],
  

here, this variable prepares the selected item, so it seraches the "slicer table" takes the selected Value, and then takes first row  ({0}) from column ID ([id]) and that variable is then used in the SQL itself.

[Anonymous]

Thanks vojtechsima for your response. I tried doing that with Direct Query, but getting the below errors:

Formula.Firewall: Query 'ImportedQueryCount' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

Tried ignoring privacy and making other changes, but got this: 

Failed to save modifications to the server. Error returned: 'DirectQuery partition 'ImportedQueryCount-ee454ffe-0c4b-4df0-9e76-c5c0db92c1cd' has '2' datasource reference(s) in its expression which is not allowed.

Any ideas?

[vojtechsima]

@Anonymous ,

well, perhaps the workaround is to create manual input of the filtering data, or it should be two compatible sources.

Normally, I would say, just write it as a function and call it insnide the main query, but you need a physical table for the slicer. So right now I don'T have any other solution.

[Anonymous]

Thanks vojtechsima -- I greatly appreciate your detailed responses.  I will keep looking for a solution, and will update this thread if I find one.

Thank you.

Claudio