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”
    

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