Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

URL Filters for on-premise reports

Hi All, We have on-premise power bi reports, We wanted to filter with URL parameters. I have tried like below. But reports are not filtering based on values. Is the feature work with on-premise serve...
On-Premise
url parameter
cirosr's avatar
cirosr
New Member
3 years ago

I recomend using below pattern to allow users to "bookmark" filters they currently have selected:

(assuming you are using On-Prem PBIRS. PBI.com cahces your last filter options, does not need the below)

 

In my reports, created as a button. Covers most used unichars transformations, add if necessary.

Generate Filter Link = 

VAR VBU = 
IF(
     ISFILTERED('Business'[BU]) = TRUE(),
     VAR ___f = FILTERS ( 'Business'[BU])
     VAR ___r = COUNTROWS ( ___f )
     VAR ___d = CONCATENATEX ( ___f, 'Business'[BU], "%27,%20%27" )
     VAR ___x = "Business/BU%20" & 
        SUBSTITUTE ( 
            SUBSTITUTE ( 
                SUBSTITUTE ( 
                    SUBSTITUTE (
                        SUBSTITUTE ( 
                            SUBSTITUTE ( IF(___r = 1, "eq%20%27" & ___f & "%27", "in%20(%27" & ___d & "%27)")
                            , "&" , "%26" )
                        , "-" , "%2D" )
                    , ")" , "%29" )
                , "(" , "%28" )
            , "," , "%2C" )
        , "/" , "%2F" )
     VAR ___y = IF( ___d = BLANK() , BLANK(), ___x)
     RETURN ___y, BLANK())

VAR VCategory = 
IF(
     ISFILTERED('CategorySegment-Filter'[CategoryCode]) = TRUE(),
     VAR ___f = FILTERS ( 'CategorySegment-Filter'[CategoryCode])
     VAR ___r = COUNTROWS ( ___f )
     VAR ___d = CONCATENATEX ( ___f, 'CategorySegment-Filter'[CategoryCode], "%27,%20%27" )
     VAR ___x = "CategorySegment_x002D_Filter/CategoryCode%20" & 
        SUBSTITUTE ( 
            SUBSTITUTE ( 
                SUBSTITUTE ( 
                    SUBSTITUTE (
                        SUBSTITUTE ( 
                            SUBSTITUTE ( IF(___r = 1, "eq%20%27" & ___f & "%27", "in%20(%27" & ___d & "%27)")
                            , "&" , "%26" )
                        , "-" , "%2D" )
                    , ")" , "%29" )
                , "(" , "%28" )
            , "," , "%2C" )
        , "/" , "%2F" )
     VAR ___y = IF( ___d = BLANK() , BLANK(), ___x)
     RETURN ___y, BLANK())

VAR VSegment = 
IF(
     ISFILTERED('CategorySegment-Filter'[SegmentCode]) = TRUE(),
     VAR ___f = FILTERS ( 'CategorySegment-Filter'[SegmentCode])
     VAR ___r = COUNTROWS ( ___f )
     VAR ___d = CONCATENATEX ( ___f, 'CategorySegment-Filter'[SegmentCode], "%27,%20%27" )
     VAR ___x = "CategorySegment_x002D_Filter/SegmentCode%20" & 
        SUBSTITUTE ( 
            SUBSTITUTE ( 
                SUBSTITUTE ( 
                    SUBSTITUTE (
                        SUBSTITUTE ( 
                            SUBSTITUTE ( IF(___r = 1, "eq%20%27" & ___f & "%27", "in%20(%27" & ___d & "%27)")
                            , "&" , "%26" )
                        , "-" , "%2D" )
                    , ")" , "%29" )
                , "(" , "%28" )
            , "," , "%2C" )
        , "/" , "%2F" )
     VAR ___y = IF( ___d = BLANK() , BLANK(), ___x)
     RETURN ___y, BLANK())
     
VAR VBuyer = 
IF(
     ISFILTERED('Buyer-Link'[Buyer]) = TRUE(),
     VAR ___f = FILTERS ( 'Buyer-Link'[Buyer])
     VAR ___r = COUNTROWS ( ___f )
     VAR ___d = CONCATENATEX ( ___f, 'Buyer-Link'[Buyer], "%27,%20%27" )
     VAR ___x = "Buyer_x002D_Link/Buyer%20" &
        SUBSTITUTE ( 
            SUBSTITUTE ( 
                SUBSTITUTE ( 
                    SUBSTITUTE (
                        SUBSTITUTE ( 
                            SUBSTITUTE ( IF(___r = 1, "eq%20%27" & ___f & "%27", "in%20(%27" & ___d & "%27)")
                            , "&" , "%26" )
                        , "-" , "%2D" )
                    , ")" , "%29" )
                , "(" , "%28" )
            , "," , "%2C" )
        , "/" , "%2F" )
     VAR ___y = IF( ___d = BLANK() , BLANK(), ___x)
     RETURN ___y, BLANK())

var linkuc = COMBINEVALUES(" and ", VBU, VCategory, VSegment, VBuyer)

var linkc0 = IF(VBuyer = BLANK(), SUBSTITUTE(linkuc, " and ", "", 4), linkuc)
var linkc1 = IF(VSegment = BLANK(), SUBSTITUTE(linkc0, " and ", "", 3), linkc0)
var linkc2 = IF(VCategory = BLANK(), SUBSTITUTE(linkc1, " and ", "", 2), linkc1)
var linkc3 = IF(VBU = BLANK(), SUBSTITUTE(linkc2, " and ", "", 1), linkc2)


Var LinkClean = linkc3

VAR FullLink = IF(linkclean = BLANK(), BLANK(), "https://PBIRS/reportfolder/YOUR_REPORT_NAME?rs:embed=true&filter=" & linkclean)

RETURN
FullLink