Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
pies
Frequent Visitor

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

Hello,

 

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:

 

pies_0-1696228411936.png

 

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:

 

 

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

 

 

 

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?!

2 REPLIES 2
AdamHawley
Regular Visitor

Not sure if this is the answer you are looking for, but I solved this issue I was facing by adding the field to the dimension area inside the query designer. At the end there is an option to add to the parameters. This added it to my parameters and created unique values from that field. 

 

AdamHawley_0-1711663011230.png

 

NicolasLJ
Frequent Visitor

I'm looking for the same use-case, did you figure out a solution ?

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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