Use Slicers for Query Parameters in PowerBI

Reading Rob Collie’s latest cool blogpost on how to retrieve slicer selections in Power BI, I couldn’t stop thinking of how awesome it would be, if we could use this technique to pass slicer selections as query parameters to the M-queries in the query editor. Not only would we have a very convenient user interface, but – what’s actually more important at the moment – we could pass multiple values as parameters to our queries, as this is not possible at all currently:

But how to fetch them? Rob’s post simply uses cross-filtering to show the values in a separate visual. In Excel we have cubefunctions where we can pass the slicer(-selection) as a parameter. Igor Cotruta, who is describing beautiful PBI-hacks on his blog here, kindly helped me out on this: “Via DMVs. Check $system.discover_sessions for the field sessions_last_command”. This worked perfectly into the following function, in which you just have to pass the name of the measure as a parameter:

Make sure that you have used that measure on one of your visuals, as otherwise the function cannot harvest it. Also you have to first save the file and then push the refresh-button in order to trigger the correct refresh. The above function sort of “reads the current PBI file from outside”, so it will only see the saved version.

When you do the first refresh, a dialogue will pop up, where you just have to accept the default values like this:

The example in the file below fetches temperature data where every selected year will create a unique URL and the results of all those calls is consolidated into one table. But of course, this technique can also be used to pass multiple parameter values to SQL-commands or others.

A final note: The query to extract the slicer parameters from the DAX-statement is not particularly robust and you might have to adjust it, if your slicer-selection-strings contain special characters.

Download for logged-in subscribers SlicerParameter2.zip

Edit 25-Sep-2017: Adjusted the code to retrieve PortID.

Also: You have to disable privacy-settings for this code to run!

Recap:

  1. If you create a new measure or adjust this file to your settings, you have to use the new measure in report somewhere and save the file. This is necessary to “initialize” the measure to your model so that it can be seen in the query editor.
  2. From then on, when you use it: Just change the selection in your slicer and click “Refresh”

Enjoy & stay queryious 🙂

Comments (5) Write a comment

  1. Hi Imke, Rob’s DAX expression actually uses direct filtering instead of cross filtering.

    ALLSELECTED(table[column])

    can be rewritten as

    CALCULATETABLE(FILTERS(table[column]), ALLSELECTED(table[column]))

    and that’s a direct filtering expression. The difference only shows itself when you have slicers or other visuals that start cross filtering the original column slicer (the one you want to fetch values from). In that case Rob’s measure will not necessarily show or return what is actually used to filter the other visuals in a power BI dashboard or report – or for that matter the values you see selected in the original slicer.

    Something to watch out for!

    PS: Nice trick getting the last command from $System.Discover_sessions

    Best regards
    Oxenskiold

    Reply

    • Thank you Oxenskiold, that’s a good DAX-learning!
      Yes, the 2nd parameter of Table.Selection needs to return true, so a strange expression like that will actually work. That was a nice surprise for me too 😉
      Kind regards, Imke

      Reply

  2. Thanks for the post, this is exactly what I’ve been looking for. I’m new to M but wanted to run my scenario by you to see if you might be able to help direct me a little: I have an API call that I want to make with a parameter value to be selected within a Power BI slicer. Could you maybe provide a little more detail on what exactly the code blocks above are doing so I can try and map this solution to my problem? Thanks!

    Reply

    • Hi Alex,
      the code block above just retrieves the selected values from a slicer and returns them as a list. There is nothing to adjust to make it work for your case.
      The downloadable file contains of an example where the parameters in this list are passed to a URL which is then called once per parameter. Did you check this file already?
      I might write a dedicated blogpost about how to setup and what to watch for when making queries like that, so I would very much appreciate if you could let me know where you’re running into problems /don’t know what to do when trying to apply this existing solution to your case.
      Thx & cheers, Imke

      Reply

  3. Thanks Imke! I somehow missed the downloadable file but am looking at it now. I will definitely post any snags I run into along the way.

    Reply

Leave a Reply