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.
Edit 25-Sep-2017: Adjusted the code to retrieve PortID.
Also: You have to disable privacy-settings for this code to run. And: This is a solution that will only work in Power BI Desktop and not in the service.
- 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.
- From then on, when you use it: Just change the selection in your slicer and click “Refresh”
Enjoy & stay queryious 🙂