PBI Report Builder - removing duplicate values in parameter dropdown

150 Views Asked by At

I'm currently working through building a paginated report with PBI Report Builder, but a small issue came up. One of my parameters is called Spend Year. Currently, for the sake of working with a faster loading file, I filtered this down to only include the year 2023. However, the issue with this is that the parameter dropdown is displaying multiple options for 2023. I believe it's displaying a 2023 for each row in the dataset that contains one.

Having multiple options for the same thing just isn't practical, so I've been looking for a way to have the parameter dropdown display distinct options. I managed to find this article: https://www.c-sharpcorner.com/article/remove-duplicate-filter-values-from-ssrs-parameter-drop-down/. While this matches my situation, the VB code that he writes is for String values. I'm working with integers. I tried my best to adapt his code to work for integers (you can find the code below), but after following through everything, my Spend Year parameter is now greyed out with no selectable values. I was sure to configure the available values as detailed in the article.

Does anyone happen to have any idea how I should go about this? If it's useful to know, my data was pulled via a DAX query.

Thank you in advance!

My version of the code:

Public Shared Function RemoveDuplicates(parameter As Parameter) As Integer()
    Dim items As Integer() = parameter.Value
    Array.Sort(items)
    Dim k As Integer = 0
    For i As Integer = 0 To items.Length - 1
        If i > 0 AndAlso items(i) = items(i - 1) Then
            Continue For
        End If
        items(k) = items(i)
        k += 1
    Next
    Dim unique As Integer() = New Integer(k - 1) {}
    Array.Copy(items, 0, unique, 0, k)
    Return unique
End Function
0

There are 0 best solutions below