Forum Discussion

AndyDD_UK's avatar
AndyDD_UK
Helper III
1 year ago

Dynamic URL based on slicer selection

In my Dim Table I have a list of items, each with a URL link to the corresponding Power BI report.

 

In Power BI report 1 I have a matrix and users click on the URL icon to open up the appropriate report.

 

Can I make the URL dynamic to pass the country, year and month slicer selection from Report 1 to the report being opened?

 

'Dim Calendar'[Year]

'Dim Calendar'[CalendarMonth]

'Dim Item'[URL]

 

Thanks

Need Help

10 Replies

  • johnt75's avatar
    johnt75
    Super User
    1 year ago

    You can create a measure like

    Dynamic URL =
SELECTEDVALUE ( 'Dim Item'[URL] ) & "?year="
    & SELECTEDVALUE ( 'Dim Calendar'[Year] ) & "&month="
    & SELECTEDVALUE ( 'Dim Calendar'[CalendarMonth] )

    You may need to change the URL parameter names to fit.

    Make sure to set the Data category of the measure to Web URL.

    • AndyDD_UK's avatar
      AndyDD_UK
      Helper III
      1 year ago

      Thanks, to add I currently have a matrix listing the items and URLs as rows...users click on the URL to open up the report....my request is pass the slicer selection(s) to the new report.

       

      My understanding is I cannot use a measure as a row. How would I use that measure ? 

      • johnt75's avatar
        johnt75
        Super User
        1 year ago

        Keep the item name on the row and add the measure as a column. You can turn on the URL Icon setting so that it only shows a link icon rather than the full URL.

  • FarhanJeelani's avatar
    FarhanJeelani
    Super User
    1 year ago

    Hi AndyDD_UK  ,

    Yes, you can make the URL dynamic based on the slicer selections in Power BI. To achieve this, you need to construct the URL dynamically using DAX expressions that concatenate the slicer values (like year, month, and country) to the base URL.

     

    Dynamic URL = 
VAR SelectedYear = SELECTEDVALUE('Dim Calendar'[Year], "2023")  -- Default to "2023" if no selection is made
VAR SelectedMonth = SELECTEDVALUE('Dim Calendar'[CalendarMonth], "01")  -- Default to "01" if no selection is made
VAR SelectedCountry = SELECTEDVALUE('Dim Country'[Country], "USA")  -- Default to "USA" if no selection is made
VAR BaseURL = SELECTEDVALUE('Dim Item'[URL], "")  -- URL from the Dim Item table

RETURN 
    IF(
        BaseURL <> "", 
        BaseURL & "?year=" & SelectedYear & "&month=" & SelectedMonth & "&country=" & SelectedCountry, 
        BLANK()
    )

    Now, in your matrix or table visual, you can add this measure to the column where you want the dynamic URL to appear.

     

    To make this a clickable link:

    • Go to the Format pane.
    • Enable URL icon (or the “Web URL”  under cell element option), depending on the visual.
    • Set the URL field to the Dynamic URL measure.

    Now, when users click on the URL icon, the correct report will open with the country, year, and month values passed as query parameters.

    Example URL Output:

    If the URL in 'Dim Item'[URL] is http//:example.com and slicer selection are:

    • Year: 2023
    • Month: 01

    Country: USA

    The final dynamic URL will look like:

    https://example.com/report?year=2023&month=01&country=USA

    This will pass the slicer values to the linked report.

     

    Please accept this as solution if it helps you. Appreciate Kudos.

    • AndyDD_UK's avatar
      AndyDD_UK
      Helper III
      1 year ago

      Thanks for this. Can't get it work though...

       

      I created the measure but it's not possible to add it as a column or row in the matrix.

       

      Also, I cannot get the Cell elements Web URL to pick up on the measure.

       

      Are you able to advise next steps ? 

  • AndyDD_UK's avatar
    AndyDD_UK
    Helper III
    1 year ago

    Thanks, I can't get that to work for my scenario...

     

    In report 1 I have a matrix with items and URL links to other reports. 

     

    In report 1, if the user clicks on item 1 in the list this will open up report 2.

     

    However I want the URL to be modified to take the slicer selections with it when report 2 is opened.

     

    For example, if the user selects 2025 from year slicer and Jan from month slicer, can report 2 be then opened also containing 2025 and Jan for slicer (not filter) ?

     

    Do you know if this is possible ?

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi AndyDD_UK 

       

      If I understand it correctly, you want the slicers in the target report (report 2) to explicitly select the values passed from the source report (report 1) when report 2 is opened, right? To my knowledge, I'm afraid this is not possible currently. 

       

      At the moment, the two options that are closest to the desired outcome are Report URL Filter feature and Cross-report Drillthrough feature. However, both of them act similarly to page-level filters or report-level filters, meaning they affect all visuals on the drillthrough page or on all pages. 

       

      The filters do not automatically apply to slicers in the target report. If you want the slicers to reflect the passed filters, you need to set up the slicers manually. 

       

      You might need to adjust your expected result. Hope this would be helpful. 

       

      Best Regards,
      Jing
      If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!