Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

Power BI Report Builder - Distinct Parameter Values while maintaining Data Model



I have read so many blog/ forum posts on this but I can't for the life of me get this working, so I really appreciate any help.


I am creating a Paginated Report, which is linked to a Power BI Dataset that has two tables which are linked together.


I want cascading parameters, and this works if I add the parameters by right clicking the Parameters folder and selecting Add Parameter, then getting Available Values from a Query pointing to the relevant table . Done this way, the selection I make in my first parameter limits the options available in the second (yay) EXCEPT that the values in the parameter drop down are not DISTINCT, so I literally have the same option appearing 20+ times:




If I add the parameter when creating my datasets (by dragging a field into the Parameters section in the Query Designer) then I get DISTINCT values, but the first parameter no longer filters the second.

To try and resolve this I have created separate tables that have DISTINCT values using a Query like the below:



    VAR DistinctValuesTable =
        SUMMARIZE (
            FILTER (
                EntityGroup[Organisation] = SELECTEDVALUE(@EntityGroupOrganisation)
            'IndicatorHandbook'[Corporate Plan / Annual Report]




Then I set the Available Values for the parameter to this table, which does remove duplicates but the issue remains that the options in subsequent parameters are not affected by earlier selections.


Any help would be greatly appreciated. I can't figure out why this would be so difficult, surely no-one wants the parameter options to work this way by default?!


Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.