Skip to main content
Microsoft Idea

Power BI

Needs Votes

DAX Query Language Visual

Vote (1742) Share
Matt Allington's profile image

Matt Allington on 04 Nov 2017 04:26:24

I see big value in creating a visual that can display the results of a DAX query. One way this could be deployed is a follows.
* Add the new visual to the report canvas
* Write a DAX query that returns a table (of course) and add this to the visual
* The visual would respect any cross filtering coming from the report just like any other visual.

This would give report writers very granular control to display records without having to use a table visual and a measure that triggers the display of the correct records.

Administrator on 30 Jul 2020 16:56:46

At the moment we don't have any plans to implement this. It sounds like it's mostly useful for learning/training? If there are other 'production' scenarios that you think it'd be useful, where a regular table/matrix wouldn't work, please let us know in the comments!

Comments (25)
Matt Allington's profile image Profile Picture

Gregory Deckler on 20 Aug 2023 00:20:57

RE: DAX Query Language Visual

Isn't this a solved problem now with TOCSV?

Matt Allington's profile image Profile Picture

Narayana Windenberger on 26 Oct 2022 20:40:44

RE: DAX Query Language Visual

The biggest win for me would be the ability to write efficient, custom-coded DAX that runs 100x faster than the SUMMARIZECOLUMNS queries that Power BI auto-generates.


Whenever I have a complex data model with many dimension and fact tables, I run into performance issues with SUMMARIZECOLUMNS. It has to do with the nature of that function and how it CROSSJOINS all the dimensions from different tables and must evaluate every measure for every combination in order to find the relevant rows to display. If I have three dimension tables with 1000 rows each, the DAX engine must evaluate 1000^3 or 1billion combinations for each measure. Yes, it's often pretty good at optimizing this, but the moment I have a measure that has any complex iterations, the DAX engine falls apart and just starts cranking through every combination taking the table visual minutes to load. Then, if I add a visual-filter to suppress certain rows, the execution time doubles as it executes the filtering query, then the original query.


I find that I'm often able to write my own query using SUMMARIZE that runs 100x faster. How cool would it be to just pop that DAX into the Table visual and enjoy amazing performance?

Matt Allington's profile image Profile Picture

Tyler Snyder on 01 Aug 2022 21:37:32

RE: DAX Query Language Visual

Key Benefits:

1) Useful to create visuals showing TopN + Others

2) Performance Optimization with variables

3) High degree of customization


Matt Allington's profile image Profile Picture

Joe Cole on 15 Jul 2022 07:10:18

RE: DAX Query Language Visual

As well as the other use cases documented here I see this as a good way of creating a flexible ad-hoc table visualisation without having to clutter the model with virtual tables and measures that might only be used once. I can think of many instances where it would be quicker and easier to conceptualise the elements needed for a particular arrangement of data as a single DAX statement than creating and adding virtual tables and measures through the UI. There may be performance / efficiency gains by generating a virtual table within the filter context of the visual rather than pre-compiling it.

Matt Allington's profile image Profile Picture

Tom Smith on 14 Oct 2021 14:42:47

RE: DAX Query Language Visual

I'm finding I need this more and more. There are workarounds, but this would be an elegant solution to so of the more demanding customer requests that cannot be built with the UI.

Matt Allington's profile image Profile Picture

Laurent Couartou on 07 Sep 2020 12:21:26

RE: DAX Query Language Visual

A simple use case for such a feature would be histograms and dynamic bucketing : how many sellers have had between 0-1000 customers , 1000-10000, … withing the current context.

This currently requires adding a dummy dimension to our model and write a dedicated measure that is more convoluted than required.

Matt Allington's profile image Profile Picture

Pedro Sanz on 06 Jul 2020 00:14:25

RE: DAX Query Language Visual

I totally agree with the last comment made on 2020 feb 5th, it could be great to make your own DAX script to avoid the issues when filtering the visual by a measure

Matt Allington's profile image Profile Picture

Narayana Power BI User on 06 Jul 2020 00:07:39

RE: DAX Query Language Visual

One of the most important use cases of a dedicated DAX table visual is performance, especially when filter a table by a measure. I've done tests where the SUMMARIZECOLUMNS query that Power BI generates for a table takes 2-3x longer than an equivalently optimized version that I write using my own DAX. Same results, slower performance.

Matt Allington's profile image Profile Picture

Anthony Yanan on 05 Jul 2020 23:52:41

RE: DAX Query Language Visual

Even in a more general sense a concept in tabular similar to Views in sql would be great. A calculated table that includes current evaluation context/does not actually evaluate until it is called. If this could be another "table type" that a user/report developer could click and drag columns from this could be even more useful.

Matt Allington's profile image Profile Picture

Luiz Florencio on 05 Jul 2020 23:47:34

RE: DAX Query Language Visual

For sure would be good (to production environment) to create complex dax measures wich returns data table. (like Dax Studio does :) )