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:

Code beautified using Lars Schreiber’s Notepad++ Script: http://ssbi-blog.de/technical-topics-english/power-query-editor-using-notepad/

 

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

Enjoy & stay queryious 🙂

Comments (2) 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

Leave a Reply