Quick groupings using Field parameters
Field parameters are a great way to give your end users the ability to customize their reporting experience in Power BI. The possibilities are enormous! Let’ me show you how easily you can make a customizable report using Field parameters and bookmarks!
Imagine a dataset like this:
Head into Power BI Desktop, to the modeling tab then “New parameter”. Select Field parameter to get the new window and give it a name. Select whatever dimensions you want available (and you are in no way limited to fields in the same table, my model only has 1 here but you can select fields freely from multiple tables to the same field parameter).
Now I can add my new field parameter “Dimensions” to a slicer to select what field to show and to a table to get the actual values.
The report user have a great possibility now to select whatever dimension they want for this table.
If the table is a Matrix instead, and the dimension is the rows, the end user can select the order of rows by selecting things from the slicer in a specific order. Selecting Product, then holding the CTRL key and selecting Country, gives this table for example.
Since the order matters (i.e. Product – Country is one combination and it’s not the same as Country – Product), with these 3 dimensions alone we’re able to create 15 combinations.
The limitation with the current setup is that you have to manually set up each combination manually. Sure, the end user can of course save a personal bookmark to get the combination back, but allow me to present a more user friendly solution.
First, create a new manual table with a combination-name on each row. Something that makes sense based on the combinations you’re about to create.
Now I’ve used this field as input for a button slicer
For this next part you need to show the Bookmark and Navigation panes.
In the Bookmarks section, create one bookmark for each group option. Right click on each of them and make sure that you have “Selected Visuals” and not “All Visuals” selected. I also deselected “Current Page” so that this can be used across multiple pages, but that’s optional.
Next, I hold the CTRL key and select the dimensions on my slicer in the order I want them to show up in the Matrix. I also make sure that the correct group is selected in my other slicer. You can validate the order is correct in the table by simply looking at the groupings on row levels.
For extra clarity, I’ve named my items like this in the Selection pane so you can more easily keep track of what is what. This is optional. Good practice, but optional 😉
All right have a close look at this now. I have clicked on the Eye-icon on the Dimension slicer so it isn’t actually visible on the screen. I have selected the the slicers and the Matrix (hold the CTRL key) and finally I right click on the bookmark with the same grouping as I made before and select “Update”.
The reason I’ve selected the Matrix too, and not just the slicers, is that I want to force update the row order and always start on the top. If you don’t there’s a risk that the you start from whatever group you had before triggering the bookmark and the user needs to drill up. Makes for terrible user experience, but feel free to try it.
Now go ahead and create a blank button
Place the button on top of the Slicer value of the group, then make sure the button doesn’t have any style enabled, no borders or anything like that. Finally, set the Action to Bookmark and select the bookmark that the button is placed on top of here.
Now you just repeat the process by copying the button and place it over each group on the slicer. Just update the bookmark under Action for each button.
With that, you’re done! The user can now select from pre-made bookmarks to decide how they want to group their matrix!
Of course you can skip the slicer with the group names and just use the buttons, but I’ve found it a bit more maintainable this way as you just have a table of groups that you can update and you have to make sure the buttons align, but you don’t need to update the style on each button to show if it’s selected or not.
Good luck!
Cheers!
About The Author
