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. And: This is a solution that will only work in Power BI Desktop and not in the service.
Recap:
- 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 🙂
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
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
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!
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
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.
HI, I get this error An error occurred in the ‘’ query. Expression.Error: There weren’t enough elements in the enumeration to complete the operation.
Details:
List. I just imported your code and ran it. Do i need to make any other changes?
Yes, I also had problems with the code today. Looks as if the measures are translated differently again, so I’ve adjusted the code.
Please check the code in the new zip-file.
Hi Imke, thanks very much for posting, I’m having an issue with running it. I’m getting an error :query. DataSource.Error: AnalysisServices: A connection cannot be made. Ensure that the server is running
I’m not sure if i’m missing something but if you could shed some light I’d be very grateful.
thanks
Mat
Hi Mat,
I haven’t used this method for a while as it will not work in the service. But the test I’ve just run worked alright.
One thing that I forgot to in the blogpost is that you should just have this pbix-file open and no other ones.
Please also make sure that you save the file before hitting refresh.
Does this help?
/Imke
i am able to download the source but facing “uery. Expression.Error: There weren’t enough elements in the enumeration to complete the operation.” error 🙁
Sorry about that, looks like the website in my sample data cannot be reached any more.
But the underlying technique should still work (for desktop).
Nevermind about my earlier comment, I wasn’t understanding completely but now I see I need the entire code. However, when I run the code I get an error dealing with the C:/Users portion and this is what it says:
We got the IO error ‘1920’ while trying to access the path ‘C:\Users\squiroga\AppData\Local\Packages\Microsoft.Microsoft3DViewer_8wekyb3d8bbwe\LocalCache\msmdsrv.port.txt’.
I am just confused what exactly is going on with that chunk that is causing the error. Again, any suggestions are very much appreciated.
Thanks.
Sorry, but I don’t have an idea what causes this error unfortunately.
No problem. I did run into one other snag with the code that I am hoping someone else has seen. I got the code working for one measure and slicer, but when I tried to add another measure and slicer it seemed to only pick up the selections from the initial slicer. I was just curious if anyone has seen this and maybe solved it, because the code seems as if it should work just fine with multiple measures and slicers.
Thanks!
If you use multiple measures, you have to call the function for each measure.
But if you want to use multiple slicers for one measure, you’d have to adjust the function code.
Hello, I was trying from long time but was not able to comment actually I am facing issue while passing measure value. It is reading measure name but not value when passed to function. Am I missing something. Please help
Not sure I understand the problem.
Have you used the measure in one of your visuals?
Hello, I am running into an issue, where the advance query editor is not recognizing the measure name. Can someone please advise!