DAX · Power BI

DAX Machine Learning Functionality Used By The Key Influencers Visual In Power BI

I’m one of those people who can’t resist peeking behind the scenes, and so when the Key Influencers visual appeared in Power BI I couldn’t help wondering how it worked its machine learning magic. Using DAX Studio to look at the DAX queries generated by the visual proved to be very revealing: it turns out that it uses a number of new DAX functions that are undocumented and probably not meant to be used outside Microsoft. For example, the following screenshot shows a DAX query generated by the Key Influencers visual that uses functions called AI.SampleStratified, AI.Train, AI.KeyDrivers and AI.ExtractProfileFilters:


Using Profiler (in a similar way to what I describe in this post) to go into even more detail about what happens when these queries run, shows that they raise the DAX Extension events that I’ve been wondering about for a long time now:

So Power BI can train and query machine learning models inside its own database engine – which, when you think about it, is pretty darned cool. And then I thought: hold on, other visuals have had machine learning features for a long time. For example, the Line Chart visual can create forecasts, but although DAX Studio shows yet another undocumented function called SampleAxisWithLocalMinMax() this does not actually seem to perform the forecasting, which I assume must be done inside the code of the visual itself:

My guess is that the functionality used by the Key Influencers visual is new functionality in the engine.

A fascinating insight into how Power BI works, but is this any practical use to us? Let me be clear: I don’t think you should be using any of these functions yourself in a real-world report. I’m sure all this would be documented and publicised if Microsoft did want us to use it ourselves! Another consideration is that these new functions return tables and that makes them awkward to use in regular .pbix Power BI reports – I guess we could create calculated tables although that’s not as flexible as returning a table from a query as shown above. That said, even though we can’t write our own DAX queries in regular Power BI reports, we can write our own DAX queries in Paginated Reports and we can now create Paginated Reports that use a Power BI dataset as a data source. I tested putting one of the queries generated by the Key Influencers visual into a Paginated Report connected to the same dataset and it worked ok (even after publishing). You can also embed DAX queries connected to a published dataset in Excel too, as I show here. Hmm, plenty to think about then…

9 thoughts on “DAX Machine Learning Functionality Used By The Key Influencers Visual In Power BI

  1. You rock Chris. I also found out that when you cluster your data in a visual Power BI uses K-MEANS function behind the science. Blogging about undocumented functionalities is really exciting. Well done 👍🏼

  2. O.O

    The implications are mind boggling.

    I wonder the probability of Microsoft ever talking about these functions publicly and its impact on Azure ML, R language, and etc…

    This would be very cool to utilize in conjunction with M, local schematic models, etc.

  3. Hi Chris, have you heard more about these functions and if they will be made available for general use? For example, I tried something similar using the new anomaly detection feature and noticed the AI logic can be used in DAX queries on a case by case basis. Next step, I’m trying to get it to work for multiple combinations (https://community.powerbi.com/t5/DAX-Commands-and-Tips/Help-with-Generate-Function-DAX-Query-Anomaly-detection/m-p/1560901#feedback-success). If you have any suggestions about this last part, they’re very much appreciated 🙂

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      I don’t think they will be available for general use, or officially supported, sorry

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.