Can we C# script to execute a query?

[samaguire]

What I want to do is replicate the behaviour in DAX Studio to preview a column's data. i.e. in TE3 right click a column and select a C# script that will open a new Dax Query pane, insert some code from template, and execute.

Is this possible?

// Generated DAX Query
EVALUATE 
TOPN( 500, VALUES('Table'[Column]) )

[m-kovalsky]

See the 'Using Selected Column(s)' section within my blog post (https://www.elegantbi.com/post/datapreview). Would be nifty for this to be out of the box in TE3 (as TE3 already has table preview).

[samaguire]

Thanks @m-kovalsky. That is a great script!

However, I ended up writing the below as I'm after the unique values in each column rather than the cross join (generally, I'm only working with one at a time) so I can check values when building filters in DAX queries.

It would be ideal if TE3 data preview was extended to support columns. Often, I'm also filtering the column values when looking for patterns, i.e. customer codes that start with a specific sequence. Being able to use the inbuilt filtering options in the data preview would be so helpful. But since this isn't available, I thought the next best thing might be to call a new DAX Query window and insert the required DAX statement as the DAX Query window results also have native filtering.

#r "System.Xml"

foreach (var c in Selected.Columns)
{
    string daxQuery = @"
        TOPN( 500, VALUES( " + c.DaxObjectFullName + @" ) )
        ORDER BY " + c.DaxObjectFullName;
    EvaluateDax(daxQuery).Output();
}

[m-kovalsky]

Ah, didn't realize you wanted each distinct set of values for a column to pop up in a new window. I was going to say the query I wrote works for getting the unique list of values for either a single column or multiple columns.

I completely agree - and I would think this would be relatively simple to add to TE3 - just a matter of priority. The use case makes total sense and it's cumbersome to have to go back to the source to find the list of unique values.

[otykier]

We have plans to add a number of script methods that you can call to invoke certain UI elements. In this case, imagine something like:

(NOTE: The code below doesn't work today - we're just looking for feedback):

var daxQueryView = UI.NewDaxQueryView("EVALUATE VALUES(" + Selected.Column.DaxObjectFullName + ")");
daxQueryView.BeginExecuteQuery();

The idea is that the first line would create a new DAX query view and populate it with the provided string. This view than has the BeginExecuteQuery() method which would asynchronously start to execute the DAX query.

Thoughts?

[m-kovalsky]

I think that would be great. Also, it would be cool to have (similar to the 'Preview Data' currently available for tables) an option for right clicking on a column (or multiple columns within the same table) and having an option to see a distinct list of values in a new window.