Power BI Parameters: Making a Parameter as Label:Value Type

0
votes

I am trying to pass a parameter to a stored procedure to retrieve a data from MSSQL source to a Power BI dataset, which is accepting specific values as an input.

Please refer the following table:

Label    |  Value
---------+-------
Male     |  M
Female   |  F

I want to show the value in the Label on the report refresh prompt, while passing the value on Value field to the Stored Proc.

The stored procedure does not accept the values in the Label field.

Anyone has a workaround for this issue?

1
powerbi

1 Answers

0
votes

You can create a text parameter with list of values that are all possible label values (i.e. Male and Female):

enter image description here

Make a table that will contain the parameter value. Get data from Blang query:

enter image description here

and use expression like this:

= #table({"EmployeeGender"},{{#"EmployeeGender"}})

Then get the data from your database, but convert the parameter value (label value) to database value (actual value) using a CASE expression like this:

case {PARAMETER} when 'Male' then 'M' when 'Female' then 'F' end

Or in M:

= Sql.Database("SERVERNAME", "DATABASENAME", [Query="select * from dbo.SelectEmployees(case '" & EmployeeGender & "' when 'Male' then 'M' when 'Female' then 'F' end)"])

With & you can concatenate strings, EmployeeGender is the name of the parameter, which will be replaced with it's value (Male or Female) and then SQL Server will execute the case statement to decode the "label value" to "actual value" (i.e. M or F).

Then place the result returned from your query and the contents of your helper table in the report. When changing the parameter's value, you will see only those rows mathing current parameter value and will see the appropriate label:

enter image description here