You Can Do MAGIC!

[Greg_Deckler]

Introduction

So I have known that I was approaching my 150^th Quick Measure Gallery submission for some time now. And I wanted to make my 150^th submission something truly special. If you know much about me from this community, you probably know that I love the Quick Measures Gallery. The Quick Measures Gallery is one of those things that I latched on to because I found myself answering what amounted to be the same question over and over again in the forums. So, I thought, what if I took this to the next level and created a Quick Measure that could answer ANY forum post? And that is exactly what my 150^th Quick Measure does.

 

MAGIC!

So, if you have followed some of my answers to forum questions, you likely know that sometimes my sarcastic side gets the better of me and in answering impossibly vague questions I inform the poster that they need to use the DAX function called MAGIC() before pointing them to the article How To Get Your Question Answered Quickly.

So, since my 150^th Quick Measure is built to answer any question, even impossibly vague ones, what better name for my 150^th Quick Measure than MAGIC! Of course, there is nothing magical about it, but the use of an advanced machine learning model that performs text analytics definitely may make it seem like magic to some!

 

How It Works

Now, I have intentionally not posted a link to my 150^th Quick Measure thus far because I think that it is important to understand how this Quick Measure submission works. Sure, you can scroll down to the bottom of this post and click the link but I think you would miss out on just how much work and effort went into this project and not fully appreciate the results you will get. So, I think you should read the entire post before trying it out but I really do not have any control over that! So if you just want to try it, scroll down to Without Further Ado below.

 

Getting the Data

The first step in the process was to get the complete data set of the Power BI forum postings for all time, almost 5 years of data thus far! Luckily I was able to work with @Sandy Rivas to procure this dataset. I will not bore you with the details of the data scrubbing that was required but I do want to sincerely thank @Sandy for her efforts in this regard. None of this would have been possible without her efforts.

Now, considering the volume of the posts in the forums, one might think that this dataset would be huge. However, it really turned out to be quite manageable because I found that all I needed from the data set was the original forum posting question and the forum posting questions marked as a “Solution”. Thus, all of the noise from other responses could essentially be eliminated!

 

Building the Model

The next step was to build the machine learning model. For this I used Azure ML Studio and the core of the model is something called the Latent Dirichlet Allocation module (https://docs.microsoft.com/en-us/azure/machine-learning/studio-module-reference/latent-dirichlet-all...). Again, I will not bore you with exactly how this algorithm works but if you really want to know, the theory behind it is discussed in this PDF. (https://ai.stanford.edu/~ang/papers/nips01-lda.pdf) In short, the Latent Dirichlet Allocation module is used to group otherwise unclassified text into a number of categories. It is also worth noting that the algorithm is specifically designed for text analytics and uses the Vowpal Wabbit library (https://github.com/JohnLangford/vowpal_wabbit/wiki/Latent-Dirichlet-Allocation), so it is extremely fast.

Here is the experiment in Azure ML Studio:

We start with the curated data set Power BI Community Forum Postings that have already been discussed. We then use the Preprocess Text module to improve the classification accuracy of the Latent Dirichlet Allocation by doing the following:

• Stop word removal
• Case normalization
• Lemmatization or stemming
• Named entity recognition

We then pass the data through our the Latent Dirichlet Allocation module and use a Boosted Decision Tree Regression to match postings marked as Solutions to original forum posting questions.

After publishing this model as a web service, I now had a model that could take any question and return an answer based upon similar questions posted over the last 5 years.

So, I could have left things here at this point and just slapped together a quick web app that interfaced with my web service and viola! But, I really wanted to make this easy for people to get their questions answered right from the Power BI Desktop interface. Luckily, the Power BI Contributors (https://powerbi.jamesdales.com/2019/12/18/power-bi-contributors-program/) program exists for this sort of thing.

 

So Many Questions…

In order to make this available from the desktop, I needed a way for users to enter their question. But Power BI Desktop is generally not a tool for end users entering ad-hoc information. Luckily, however, one visual does allow this and that is the Q&A visual. And it is designed for entering questions already, so it was a natural fit.

Now, the trick was that I needed a way to override the Q&A visual’s “normal” behavior and instead make a call out to my custom machine learning web service. This is where the Power BI Contributors Program came in. With a few simply modifications to the Q&A visual, you can now override it’s default behavior to call a custom web service as shown below:

One simply needs to use the slider to turn the Custom Model setting to on and then enter the Request URI of the published web service along with the corresponding API Key.

Turning on the Custom Model setting of the Q&A visual modifies it’s presentation so that it now displays a large ASK button that, when clicked, invokes the custom model and returns the result within the specified measure, in this case QA Result.

 

One Last Thing

So, again I could have stopped here at this point. But, I wanted to make this 150^th Quick Measure really special. So, I created a quick and dirty custom visual that would format the answer in a nice way. The visual detects if DAX code is included in the answer and, if so, it verifies, formats and displays the DAX code using a call out to the DAX Formatter by SQLBI, https://www.daxformatter.com/. Otherwise, the visual switches to just display the text of the answer.

 

Without Further Ado

OK, you’ve suffered through some of the gory details behind all of the work and effort that went into making my 150^th Quick Measure Gallery submission. Go try MAGIC yourself and let me know what you think!

 

 

 

 

 

Conclusion

I really think that this amazing quick measure would simply not have been possible without starting from the premise of the “Art of the Possible”. If you have never read the book, then I can tell you that it is certainly worth the read. And it is only fitting because, honestly, this entire article is simply just that, an expression of what could be possible. It’s all a complete fabrication. I mean ALL of it. Really. I haven’t even read that book for crying out loud. Even that is fake. Do you really think that Microsoft would let me anywhere near the Power BI Contributor’s program? They aren’t bananas nuts crazy people!! So, if you are just reading this now after coming back here to flame me, I do want to point out that I did ask you up front to read the ENTIRE article. So, anyway, before you flame me, I hope you have enjoyed this in the spirit with which it was intended. Sometimes we all just need a good practical joke in our lives. 😊

[AllisonKennedy]

Love it! Keep up the great work @Greg_Deckler 

[UAV-Test-Pilot]

I didn't realize April was so close to June