Eliminating duplicate parameter entries in PBI Report Builder

[straubja]

Hello,

I have a paginated report which has one parameter (order number).  However, when I run the report it shows duplicate values for the order number in the dropdown (over 3000) but there are only 165 distinct order numbers in the table.  I have reviewed the standards regarding parameter use and believe I am following them.  Any advice on how to eliminate duplicates in the dropdown?

Thanks

[straubja · Accepted solution]

Just to close this loop, a coworker was able to determine the root cause of the problem.  I had copy-pasted the query from PBI Desktop (bypassing the wizard) and did not realize that I had to assign the report parameter manually.  Below is the snippet with the missing code highlighted.

Hopefully this is helpful to others.

[straubja]

I added a second query to get a distinct set of values from the 'order number' field, and when I use that for the available values it gives me a clean list, but the relationship between the two datasets is not functional and I don't know how to change that.  I can replicate a non-paginated version of what I want in Desktop, but not in Report Builder.  Any suggestions?

[d_gosbell]

---

@straubja wrote:

but the relationship between the two datasets is not functional and I don't know how to change that.

---

There is no such thing as relationships between different datasets in a paginated report. And it would not be needed in this case anyway. The user selects a value or values from a parameter and those are passed to the main dataset. As far as the main dataset is concerned it does not care if those values originated from a second dataset or if they were manually typed in.

You could possibly try using some sort of profiling tool for your data source to see how the parameter values are being passed through, 

[d_gosbell]

The parameter values are populated from a query. If you are seeing duplicates it is due to an issue with the data or with your query. If the parameter dataset was auto-generated you can access it by right-clicking on the datasets folder in Report Builder and then choosing the "Show Hidden Datasets" option. You would then need to alter the query to do some sort of "group by" or distinct operation to eliminate the duplicates