Power BI’s DAX language is often described as “simple but not easy.” While visuals such as tables and matrices generate a lot of DAX behind the scenes, such as subtotals and totals by dimension, it can be a challenge to write that code yourself. The experts at SQL BI made a proposal for visual calculations in DAX, and there’s now a way to almost achieve what they’re suggesting, if in a roundabout way.
Microsoft added a REST API to execute DAX queries against a dataset. Don’t be scared by the term “API” if you’re not a developer, we have a no-code solution for you. When this API was released for general availability in May 2022, Microsoft added an action to Power Automate that calls said API without having to create your own custom connector. This allows both bulk export to CSV (up to 100K rows or 1M values) but also the execution of pointed queries to get specific aggregates generated by visuals.
How? Just copy and paste the DAX query behind the visual of your choice courtesy of Power BI Desktop’s built-in Performance Analyzer. You just need to build the table or matrix that spits out the aggregates according to your desired structure, including reflecting filters such as Top N.
Now that you have your DAX query, put it in the Power Automate action and run your flow once to check that it runs successfully. Once that’s done, edit your flow again to add a step to parse the JSON data returned by the query step. You can generate that action from a sample that you’ll obtain by reviewing in the flow’s execution history the raw outputs of the query step.
This initial JSON includes headers, where you’ll find various properties such as the data types of the results returned by your query, and a body which is really what we’re interested in here. So we add another Parse JSON step that will get the values out of the previous one’s body. Or you could parse the Power BI’s request’s First table rows instead of its Body, in which case you’ll save a step but won’t have the request headers.
Now you have a set of structured results that you can save in a file, post in a Teams notification, or use however else you’d like. You could even ingest that data back into your Power BI data model if you’d like to re-use these values in other visuals, similarly to SQL BI’s “visual calculations”. It’s not immediate, but it might beat spending hours debugging that elusive DAX calculation to return a number you’re already seeing in one of your visuals.
So if you wanted to display a single number from a matrix in a separate card or gauge without writing any DAX, well now you have a way to do so. Of course what we’ve retrieved are static numbers, not dynamic measures, so this technique not a substitute to feed into interactive visuals that should respond to further filtering, but this may save you in a pinch for some static reporting needs such as emails and notifications.