Field Parameter and Bookmarking Field Parameter Values In Power BI Report Server

[anandav]

What is Field Parameters?

Power BI service in May 2022 released the Field Parameters feature.

As explained in the Microsoft site... "Field parameters allow users to dynamically change the measures or dimensions being analyzed within a report. This feature can help your report readers explore and customize the analysis of the report by selecting the different measures or dimensions they're interested in."

This is a very useful feature but unfortunately it is not yet available in Power BI Report Server (PBIRS) - as of June 2022.

So in this article, I will show how to achieve "Field Parameter" like feature in Power Report Server and how Bookmark like functionality to save Field Parameter values can be achieved. There are limitations in this approach which I will cover later, but will be useful in many use cases.

Below is what we will build in PBIRS

There are three main techniques to achieve "Field Parameter" LIKE feature in PBIRS:

1. How to change the displayed measures in visuals dynamically?
2. How to change the DIM attribute or Group By attribute of visuals dynamically?
3. How to Bookmark the selected values of the "Field Parameters"?

I will use the Adventure Works Excel data that I downloaded from Radacad. But you can use any simple data model with a couple of DIM tables and a Fact table.

How to change the displayed measures in visuals dynamically?

There are many articles and Youtube videos on this subject. Therefore I am not going to cover the technique in detail but suffice to say it uses the "disconnected table" technique.

To load data into the disconnected table used to select the measures dynamically, I used an Excel sheet to structure the data to suit my requirement.

There are few advantages in having this as Excel or CSV file.

1. I can create the required hierarchy to group the measures .

2. I can customise what should be displayed in the visual Title for the selected measure.

3. I can have a single value to use for Bookmarks (explained later in this article).

Then I loaded the above Excel table into  the table myKPIsHierarchy. That is  the first step to selecting measures dynamically.

Next, I created the below DAX measure to select the appropriate measure based on the user slicer selection to dynamically change the displayed measure.

_Selected KPI =
SWITCH (
    TRUE,
    AND (
        SELECTEDVALUE ( myKPIsHierarchy[KPI Group] ) = "AdventureWorks",
        SELECTEDVALUE ( myKPIsHierarchy[KPI] ) = "Sales Amount"
    ), [_Sales Amount],
    AND (
        SELECTEDVALUE ( myKPIsHierarchy[KPI Group] ) = "AdventureWorks",
        SELECTEDVALUE ( myKPIsHierarchy[KPI] ) = "Customer Count"
    ), [_Customers In Sales],
    AND (
        SELECTEDVALUE ( myKPIsHierarchy[KPI Group] ) = "Loyalty Club",
        SELECTEDVALUE ( myKPIsHierarchy[KPI] ) = "Loyalty Club Sales"
    ), [_Loyalty Sales],
    AND (
        SELECTEDVALUE ( myKPIsHierarchy[KPI Group] ) = "Loyalty Club",
        SELECTEDVALUE ( myKPIsHierarchy[KPI] ) = "Loyalty Club Customers"
    ), [_Loyalty Customers]
)

In the above code, _Sales Amount is a simple SUM(SalesAmount) measure. Other measures are similar measures to calculate Customer Count, Loyalty Sales, etc.

The final step is to create a measure select the Heading to be displayed from the disconnected Measures table so it can displayed on visuals using the visual Title 'function' capability.

_Selected Heading =
IF (
    HASONEVALUE ( myKPIsHierarchy[Heading] ),
    MAX ( myKPIsHierarchy[Heading] ),
    "Please select a KPI"
)

Now we can select the measure to be displayed in the visual dynamically and also change the visual Title dynamically based on which measure is being displayed.

How to change the DIM attribute or Group By attribute of visuals dynamically?

Again, there are various article and Youtube videos on this subject. The one found very interesting is this video from BI Elite.

Most articles and videos use the TREATAS technique which could have some performance impact when you are dealing with millions of records in a DIM and 100's of millions of records in Fact table. The video from BI Elite overcomes this issue by creating inactive relationships between the disconnected DIM attributes table and the relevant DIM tables. I found this to be a more appropriate technique for large data models.

Again, disconnected table comes to rescue. There are many approaches explained in internet articles and videos on how this table can be created based on what Dim attributes we want to make dynamic. I opted to create the required values in Excel (or CSV) as I could structure the data to me requirements.

(Showing only a few rows as example)

I created the disconnected GroupBy table for the DIM attributes using the above Excel table.

Next the most important step: create the non-active relationships in the data model between the Dim Attributes table and the relevant DIM tables. Please note the direction of one to many relationships and the direction of filter.

Once again, a big thank you to BI Elite for the video.

After the non-active relationships are setup in the model, the final step is the DAX measure to select dynamically select the Dim attribute to display in the visual - with a twist!

What enables the Field Parameter like feature is that this technique uses the previously created dynamic measure selection measure in the measure to select the Dim attribute dynamically.

_Selected Group By =
SWITCH (
    TRUE (),
    SELECTEDVALUE ( 'GroupBy'[Group By] ) = "Occupation",
        CALCULATE (
            [_Selected KPI],
            USERELATIONSHIP ( 'GroupBy'[Group By Values], DimCustomer[EnglishOccupation] )
        ),
    SELECTEDVALUE ( 'GroupBy'[Group By] ) = "Product Category",
        CALCULATE (
            [_Selected KPI],
            USERELATIONSHIP ( 'GroupBy'[Group By Values], DimProductCategory[EnglishProductCategoryName] )
        ),
    SELECTEDVALUE ( 'GroupBy'[Group By] ) = "Product Sub-Category",
        CALCULATE (
            [_Selected KPI],
            USERELATIONSHIP ( 'GroupBy'[Group By Values], DimProductSubcategory[EnglishProductSubcategoryName] )
        )
)

This is the measure that we will add as value field in visuals.

e.g. In the bar chart, the field setup is as follow:

To complete the "Field Parameter" like feature, we just need one final measure to combine headings for the measure selected and the Dim Attribute headings.

_Full Heading =
[_Selected Heading] & "  By  "
    & SELECTEDVALUE ( 'GroupBy'[Group By] )

Now we can test the full "Field Parameter" like functionality in PBIRS.

You can use the Format --> Edit Interaction option to use the same dynamic measure and Dim Attribute selection slicers to work with two different visuals to enable end users to compare two different measures.

 

How to Bookmark the selected values of the "Field Parameters"?

The final technique is to mimic the Power BI service Bookmark functionality in PBIRS.

For this I used two techniques:

1. URL parameters
2. A bookmark folder in the browser (in my case MS Edge browser)

We can use individual fields value as url parameter to the PBIRS PBI reports.

http://Your_PBIRS_Server_Name/Your_Reports_Foolder_Name/powerbi/Your_PBI_Report_Name?filter=myKPIsHierarchy/KPI Group eq 'Loyalty Club' and myKPIsHierarchy/KPI eq 'Loyalty Club Sales'

But it makes it too complicated and long. This is where the Bookmark ID column in the Measures table comes in handy. Instead of the combination of two column values, now we can just pass only the BookmarkID column value to achieve the same results.

http://Your_PBIRS_Server_Name/Your_Reports_Foolder_Name/powerbi/Your_PBI_Report_Name?filter=myKPIsHierarchy/BookmarkID eq 'BM003'

Now, all we need to do is to create a Bookmarks folder in the browser and add the relevant urls to the bookmarks folder.

This is not an ideal solution for end users, but the power users could create the standard combinations of measures and Dim attributes frequently used and create the bookmarks urls to be shared with other users. Not ideal, but in my case an acceptable workaround!

Conclusion

By using the common disconnected table and url parameters technique, I have created a "Field Parameter" LIKE feature and Bookmark LIKE feature in Power BI Report Server.

The major drawback compared to Power BI service Field Parameters is that we cannot select more than one measure to be displayed. Though this is a major drawback of this approach, if the use case is simply to display one measure dynamically across mutliple visuals, this works!

Ps. if you use two sets of dynamic measures and Dim Attributes to enable users to compare values as explained earlier in this article, instead of using Edit Interactions to control slicer filtering, you may want to consider another set of tables for dynamic measures and Dim attributes. This way you will be able to use url parameters to bookmark all the slicer selections!