• Home
  • About Us
  • Contact Us
  • Privacy Policy
  • Special Offers
Business Intelligence Info
  • Business Intelligence
    • BI News and Info
    • Big Data
    • Mobile and Cloud
    • Self-Service BI
  • CRM
    • CRM News and Info
    • InfusionSoft
    • Microsoft Dynamics CRM
    • NetSuite
    • OnContact
    • Salesforce
    • Workbooks
  • Data Mining
    • Pentaho
    • Sisense
    • Tableau
    • TIBCO Spotfire
  • Data Warehousing
    • DWH News and Info
    • IBM DB2
    • Microsoft SQL Server
    • Oracle
    • Teradata
  • Predictive Analytics
    • FICO
    • KNIME
    • Mathematica
    • Matlab
    • Minitab
    • RapidMiner
    • Revolution
    • SAP
    • SAS/SPSS
  • Humor

Creating a Hyperlink with filters through DAX

August 29, 2018   Self-Service BI

Thanks to two recent Power BI features it now possible to generate a link on the fly using DAX to go to a new report and pass in any filters. (Alsop imagine linking to your favorite SSRS report !)

In this blog post we will create a DAX formula that generate a hyperlink based on the filters in the report. The measure contains 2 parts, the first part is generating the right url to the target report using DAX and the second part is passing the filters to that report.  

We will be using variables a lot in these expressions, more on variables in DAX can be found here: https://msdn.microsoft.com/en-us/query-bi/dax/var-dax

Lets dive in, we start with declaring the measure and creating a variable to declare the MAX url length we want to check for.  The MAX url length supported by browsers is defined at 2040 but we are taking 2020 to be safe.

Static URL Measure =
// Setting the MAX URL length to 2020 to be safe.
VAR maxURLLength = 2020 

Now we determine the URL, we do this in 2 ways, one we hardcode the URL second we determine the URL based on filters in the model. So potentially we can jump to different pages based on the filters. 

To conclude the URL we use a fake filter that makes dynamically appending others filters to the URL easier later on, otherwise you will have to do a lot of magic :). The fake filter refers to a table and column in the model created for just this purpose and it is not related to any other tables so setting it doesn’t have any effect, except to make crafting the URL  easier. If you don’t add these fields to  the model the URL will return an error.

Lets create the URL part of the measure: 

  1. We use a single hardcoded URL for each page:
    VAR reportURl =
    “https://app.powerbi.com/groups/a4969cce-a7ed-4868-82c9-0c8901ab83c2/reports/6c48c42d-ec49-472c-a34f-3491ebdf7052/ReportSection1?filter=a/a=1”
    Observe we added a/a=1 here just to get the filter started, now all we have to do is append.
  2. In the 2nd option we construct the URL based on the selection of filters in the report, each selection generates a different URL.
    We use a DAX switch statement to test which filters are set. Using the ISFILTERED function we can check if a filter has been set on a column. In this case we base the URL on certain filters set in the report so we can steer users based on their selection. Each combination of filters targets a different URL.

    This looks like this:
    VAR reportURl =
    SWITCH (
        TRUE (),
        //Are there filters on education and currency?
        AND (
            ISFILTERED ( Customer[English Education] ),
            ISFILTERED ( ‘Currency'[Currency Name] )
        ), “https://app.powerbi.com/groups/a4969cce-a7ed-4868-82c9-0c8901ab83c2/reports/5g4tc42d-ec49-472c-a34f-3491ebdf4321/ReportSection1?”,
        AND (
            ISFILTERED ( SalesTerritory[SalesTerritoryCountry] ),
            ISFILTERED ( ‘Currency'[Currency Name] )
        ), “https://app.powerbi.com/groups/a4969cce-a7ed-4868-82c9-0c8901ab83c2/reports/7c48c42d-ec49-472c-a34f-3491ebdf6543/ReportSection1?”,
        //define the fallback URL 
        “https://app.powerbi.com/groups/a4969cce-a7ed-4868-82c9-0c8901ab83c2/reports/8r65c42d-ec49-472c-a34f-3491ebdf7564/ReportSection1?”
    ) //use a hidden table\column in the model to use the filter to make it easier to add other filters
        & “filter=a/a=1”
     

 Creating a Hyperlink with filters through DAX

Now finally when the URL is defined we need to pass in all the filters based on the selection on the screen. This part can be split in some sections, one 
In the  first part we check which filters are set and create parts of the URL. The URL Syntax for each filter is “table/column IN (‘value1’, ‘value2’).

To construct the filter URLs we use ISFILTERED to check of a filter has been set, if that is the case we create a URL filter that starts with the corresponding Table / Column name and the IN clause to pass the list of values we need to filter on. We use CONCATENATEX to concatenate all the selected values using the VALUES function. We put the values between ‘sign as they are a string in this case. Finally add the, sign to separate each value.

This will generate the URL filter for these selected filters. In this sample we add a few different filters.  We start looking at Country and generate the query string:

VAR countryfilter =
//When the column is filtered craft the filter part and add all selected values of that column
IF (
    ISFILTERED ( SalesTerritory[SalesTerritoryCountry] ),
    “Store/Territory in (‘”
        & CONCATENATEX (
            VALUES ( ‘SalesTerritory'[SalesTerritoryCountry] ),
            ‘SalesTerritory'[SalesTerritoryCountry],
            “‘,’”
        )
        & “‘)”
)

We do the same for the date range, in this case we use MIN and MAX to get the range of values of the selection and we use le for Less or Equal and ge for Greater or Equal. Dates do not take the ‘ sign.

VAR datefilter =
//For date we take the max and min values of the selection to determine the < and >
IF (
    ISFILTERED ( ‘Date'[FullDateAlternateKey] ),
    “Date/FullDateAlternateKey le “ & MAX ( ‘Date'[FullDateAlternateKey] )
        & ” and Date/FullDateAlternateKey gt “
        & MIN ( ‘Date'[FullDateAlternateKey] )
)

We repeat this for all the filter we want to pass along to the new report. 
After all the filters are generate we  add them all to the URL. As the max URL length is set to 2020 we want to create some fallback filters in case not all the filters fit the max length. We start with creating a few filters and add more filters to the list. If a filter is not empty we need to add  “and” to the URL to start a new filter. Each list of filters will be longer and longer.

VAR URLfilter4 =
reportURl //Create the URLs, add backup URLS in case the length is too long with less filters
    & IF ( LEN ( countryfilter ) > 0, ” and “ )
    & countryfilter
    & IF ( LEN ( datefilter ) > 0, ” and “ )
    & datefilter
VAR URLfilter3 =
URLfilter4 //Build upon the short filter to expand on it until we reach the URL limit
    & IF ( LEN ( educationFilter ) > 0, ” and “ )
    & educationFilter
VAR URLfilter2 =
URLfilter3
    & IF ( LEN ( promotionFilter ) > 0, ” and “ )
    & promotionFilter
VAR URLfilter1 =
URLfilter2
    & IF ( LEN ( currencyFilter ) > 0, ” and “ )
    & currencyFilter

Finally we return the full URL to the measure to be used on the report. Based on the length of the filter URL we determine which one we need to return. If we do not have ANY url that fits the bill we will throw an error to the user. In that case he will not see the URL but an error in the report that he can click on with the supplied message.

SWITCH (
    //Return the URL to the measure based on the length of the URL
    TRUE (),
    //Is URLfilter1 smaller than the max URL length?
    AND (
        LEN ( URLfilter1 ) >= 0,
        LEN ( URLfilter1 ) <= maxURLLength
    ), URLfilter1,
    //Is URLfilter2 smaller than the max URL length?        
    AND (
        LEN ( URLfilter2 ) >= 0,
        LEN ( URLfilter2 ) <= maxURLLength
    ), URLfilter2,
    //Is URLfilter3 smaller than the max URL length?
    AND (
        LEN ( URLfilter3 ) >= 0,
        LEN ( URLfilter3 ) <= maxURLLength
    ), URLfilter3,
    //Is URLfilter4 smaller than the max URL length?
    AND (
        LEN ( URLfilter4 ) >= 0,
        LEN ( URLfilter4 ) <= maxURLLength
    ), URLfilter4,
    //If none of them fit the URL lengths  throw an error to the user
   “” & ERROR ( “We are unable to create a link, please remove some filter values and try again” )
)
Observe we are doing “” & ERROR here, this will make sure the data type of the measure is a text, which is needed to make the measure return TEXT so we can mark it as web url. 

The final result will be a URL like this:

https://app.powerbi.com/groups/a4969cce-a7ed-4868-82c9-0c8901ab83c2/reports/6c48c42d-ec49-472c-a34f-3491ebdf7052/ReportSection1?filter=Store/Territory in (‘United States’,’Canada’,’France’,’Germany’,’Australia’,’United Kingdom’) and Date/FullDateAlternateKey le 9/23/2008 and Date/FullDateAlternateKey gt 9/23/2008 and Customer/Englishx0020Education in (‘Bachelors’,’Partial College’,’High School’,’Partial High School’,’Graduate Degree’) and Promotion/EnglishPromotionName in (‘No Discount’,’Volume Discount 11 to 14′,’Volume Discount 15 to 24′,’Volume Discount 25 to 40′,’Volume Discount 41 to 60′,’Volume Discount over 60′,’Mountain-100 Clearance Sale’,’Sport Helmet Discount-2002′,’Road-650 Overstock’,’Mountain Tire Sale’,’Sport Helmet Discount-2003′,’LL Road Frame Sale’,’Touring-3000 Promotion’,’Touring-1000 Promotion’,’Half-Price Pedal Sale’,’Mountain-500 Silver Clearance Sale’)

Finally we need to set the data category of the measure to Web URL 

resulting in a clickable hyperlink in Power BI based on the filters:

Note: a small warning, if your URL is not really a URL the clickable link will not work (I found this out the hard way, I had a space as first character and the link didn’t work).

You can download the sample workbook here.

Let’s block ads! (Why?)

Kasper On BI

Creating, Filters, Hyperlink, Through
  • Recent Posts

    • Derivative of a norm
    • TODAY’S OPEN THREAD
    • IBM releases Qiskit modules that use quantum computers to improve machine learning
    • Transitioning to Hybrid Commerce
    • Bad Excuses
  • Categories

  • Archives

    • April 2021
    • March 2021
    • February 2021
    • January 2021
    • December 2020
    • November 2020
    • October 2020
    • September 2020
    • August 2020
    • July 2020
    • June 2020
    • May 2020
    • April 2020
    • March 2020
    • February 2020
    • January 2020
    • December 2019
    • November 2019
    • October 2019
    • September 2019
    • August 2019
    • July 2019
    • June 2019
    • May 2019
    • April 2019
    • March 2019
    • February 2019
    • January 2019
    • December 2018
    • November 2018
    • October 2018
    • September 2018
    • August 2018
    • July 2018
    • June 2018
    • May 2018
    • April 2018
    • March 2018
    • February 2018
    • January 2018
    • December 2017
    • November 2017
    • October 2017
    • September 2017
    • August 2017
    • July 2017
    • June 2017
    • May 2017
    • April 2017
    • March 2017
    • February 2017
    • January 2017
    • December 2016
    • November 2016
    • October 2016
    • September 2016
    • August 2016
    • July 2016
    • June 2016
    • May 2016
    • April 2016
    • March 2016
    • February 2016
    • January 2016
    • December 2015
    • November 2015
    • October 2015
    • September 2015
    • August 2015
    • July 2015
    • June 2015
    • May 2015
    • April 2015
    • March 2015
    • February 2015
    • January 2015
    • December 2014
    • November 2014
© 2021 Business Intelligence Info
Power BI Training | G Com Solutions Limited