21 Sep’17

BIDynamic URL Filtering on Power BI

Introduction:

There may arise a situation where we may want to link one report to a more detailed report dynamically in Power BI. Although we can link dashboards to reports and also provide a custom link for a tile in a dashboard, report to report linkage is currently not a feature available in Power BI.
There is a way to achieve this by using URL filtering. Below is a report which shows Card transaction details based on Expense Type. We want to link this report to a more detailed report after clicking on the URL link icons in the table.

You can download the sample CSV file from here http://bit.ly/nitincsv1

After clicking on the URL Link icon a report like this should open up which is filtered by Expenditure Type.

Steps:

The steps to achieve this are mentioned below:

1. We first need to understand how URL filtering works. The syntax for URL filtering is
URL?filter=Table/Field eq ‘value’
Table and Field names are case sensitive
Value should be put in single quotes

The Table and Field can be found under Fields in Power BI Desktop

The URL can be found on the web page in Power BI Online. Every page in a report has a unique URL in Power BI.  You can find it in the browser address bar of the report.

The URL filter for filtering ExpType in CardDetails for the value ‘Bills’ would be,
https://app.powerbi.com/groups/me/reports/6ea11c00-85ca-4b8e-907a-42979eaadcaa/ReportSection1?filter=CardDetails/ExpType eq ‘Bills’

2. The above is a very static example for filtering ExpType but this can be made much more dynamic by using DAX
Create a new Calculated Colum for CardDetails

 

3. Enter the following line as the DAX Code:

Link = “https://app.powerbi.com/groups/me/reports/6ea11c00-85ca-4b8e-907a-42979eaadcaa/ReportSection1?filter=CardDetails%252FExpType%20eq%20%27″&CardDetails[ExpType]&”%27”

In the above function we have put the ASCII values for blanks, apostrophes and equal to sign for ensuring that the URL works efficiently

• Blank -> %20
• Equal -> %252
• Apostrophe -> %27

We append the CardDetails[ExpType] in place of value in the URL Syntax. The rest of the URL remains the same. After pressing Enter the column generated will look something like this in a table visualization

4. The String generated in our column needs to be converted into a hyperlink. Click on Link in Fields, Go to Modeling and select Data Category as Web URL

The Link Column should now look like this,

5. We can convert the hyperlink into a more pleasant URL link icon by going to

Visualizations->Format->Values->URL Icon(Turn On)

6. The final Report generated looks like this

7. Clicking on the URL icon will dynamically filter a new detailed report in a new tab in your browser. To confirm whether the report has been filtered or not, we can look at the Filters section in Power BI Online as well as the URL.

Conclusion:

As you can see, we have successfully created a Report with Links which on being clicked generates a detailed report with dynamic filtering.

Written by

Shannon Pereira

Software Engineer

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload the CAPTCHA.

Want to streamline your business processes?

  • This field is for validation purposes and should be left unchanged.

Recent Articles

  • Reading more then 10K records in D3FOE OData API

    11 July’ 2018

    Introduction: We all know Dynamics 365 Finance and Operations has limitation of 10K records to be fetched at a time usi...

    Read more
  • Paging in D365 Customer Engagement v9.0

    10 July’ 2018

    Introduction: The Xrm.retrieveMultipleRecords method is used to retrieve a collection of records in Dynamics 365 Custom...

    Read more
  • Set up Dynamics 365 connection in Microsoft Social Engagement

    10 July’ 2018

    Introduction: This blog explains how to Set up Dynamics 365 connection in Microsoft Social Engagement. Steps to be follo...

    Read more
  • Voice of the Customer failed to install

    10 July’ 2018

    Introduction: Many people face issues in installing Voice of Customer solution on v9 environment and trying repeatedly ...

    Read more
  • Scribe Insight AX as a Web Service Find Block issue

    10 July’ 2018

    Introduction: If we need to look up for any value from AX then we do it by using a Find Block in Scribe Insight Eg: Basi...

    Read more

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close