How do I Call a Power Query M Function from a DAX Measure?

[Shawn_Eary]

I have a parameterized Power Query M function that I want to call from inside a DAX Measure.  How might I do that?   

When I try to reference the said Power Query M function in the DAX Measure editing window, I can't see it in intellisense.  I'm trying to do this because my Power Query M function contacts a REST service to get the data using URL parameters.  While I am able to change the pameter for the Power Query M function and see my visualizations update, I seem to be required to perform that update via the Power BI "IDE".  Instead of using the Power BI "IDE", I want be be able to change the Power Query M parameter via a Slicer visualization inside the report.  I keep running into trouble with this.  The best example I've seen is here:
https://blog.pragmaticworks.com/power-query-controlling-m-query-functions-with-user-driven-parameter...
Unfortunately, I seem to be misunderstanding something.
 

[amitchandak]

@ImkeF , can you help

[ImkeF]

Hi @Shawn_Eary ,

that's not how Power BI is supposed to work, as with every changing selection, you'd  trigger a refresh of the data model.

But in Power BI Desktop, it's actually possible to implement this behaviour with the hack I've described here: 

https://www.thebiccountant.com/2017/03/22/use-slicers-for-query-parameters-in-powerbi/ 

Just keep in mind that this will NOT work in the service!

[Shawn_Eary]

@ImkeF- I ran across your post also, but I'm very sorry that I forgot to mention it.  That is very embarrasing.  I try to mention all of the people that give me help but I was so confused yesterday that I must have misplaced your post in all of the "shuffle".  In any case, I'm having a hard time getting it to work for me in Power BI Desktop.  Also, I really need something that will work in Power BI Service.

So you are saying that there is absolutely no way to call a Power Query M Function from DAX?

AGAIN: My sincere appologies for not citing you yesterday.  My lack of dillegence on that matter almost makes me sick to my stomach as if this had been an acadmic paper I could have gotten dismissed...

[ImkeF]

Hi @Shawn_Eary ,

no worries at all. There's so much out there on Power BI meanwhile that's impossible to keep track.

Yes, my solution works only in Desktop and I'm not aware of any solution in the Service.

Also imagine how fast you would have reached the daily refresh limit - I really don't think that it's a good idea to run things like that there.

How about creating a big table with all possible results and an additional column with the URL-parameter in it that you can then use for a simple native filter?

[Shawn_Eary]

I was just able to call a parameterized Power Query M function from an Excel Workbook using an Excel cell to specify the parameter:
https://devinknightsql.com/2015/04/03/power-query-controlling-m-query-function-with-user-driven-para...
(Devin Knight)

But I haven't got your method to work for me yet:
https://www.thebiccountant.com/2017/03/22/use-slicers-for-query-parameters-in-powerbi/ 

(Imke Feldmann)

[v-chuncz-msft · Accepted solution]

@Shawn_Eary 

Alternatively, you may try Create Power BI visuals with R.

[Shawn_Eary]

The post by v-chuncz-msft doesn't technically answer the question I asked, but it's close enough.  I think the point is that I'm going to need to create a custom visualization to do what I need.  It's possible I may have asked a "bad question"...  If I become aware of a better solution later, I will try to update this thread.

[Shawn_Eary]

@v-chuncz-msft- I tend to agree with you.  For this edge case, I am looking into creating a custom visual.  I briefly looked at R for this; however, given my need to collect input via GUI, it seems the JavaScript API might work better for me:
https://docs.microsoft.com/en-us/power-bi/developer/visuals/custom-visual-develop-tutorial

Not sure if I've said this already, but I essentially want to do the following:

1. Display a Slicer like control to allow the user to select a minimal GPA
2. Call a JSON REST API once a selection has been made from that slicer passing the GPA in as a parameter
3. Display that data to the user via SVG (or easier visualization method) without importing the data into Power BI

This would be easy to do using a plain old ASP.NET Website and maybe more straightforward if I were to do it in SharePoint, but we don't want to get into the reasons why I want to implement this in Power BI right now.

Ideally, the Power BI service would allow me to attach code to the native slicer so I could grab the data form the JSON REST API and update the report without having to create a Custom Visual.  Unfortunately, my institution may want to go through a REST API (with query parameters) to collect data.  Given the current design of Power BI Service, I may have to write numerous custom visualizations.  

OFF TOPIC: It would be really useful if the developers would enable spell sheck for this forum input element.  In HTML5, I think you just need to flip a switch to turn that on:
https://www.w3schools.com/tags/att_global_spellcheck.asp
https://www.wufoo.com/html5/spellcheck-attribute/
Grammar check would be aswsome as well, but I don't think HTML5 supports that yet 😁