![]() ![]() In Tabular Editor we can see the Entity table only has four columns ![]() Then to ran Tabular Editor 2 we click on its icon from the External Tools ribbon of Power BI This is the report built using the script generated parent-child hierarchy Loading and Executing The ScriptĪs a start point I used a small model containing only three tables, that I saved as the “Parent-Child Hierarchy Scripting with Tabular Editor before.pbix” file. Since I wrote the script using variables to contain the names in the model, it should be easily adapted to other models too, but I still have to try it. My conclusion is that this kind of script can be written and works smoothly, but it requires to enable the unsupported features of Power BI in settings of Tabular Editor. This, together with the fact that I quickly forget how to use the PATH* functions, since I use them only when dealing with parent-child hierarchies, gave me the idea to write a script to automatically generate parent-child hierarchies.Īs a test project I decided to implement a script to re-create the basic parent-child DAX pattern described in the Parent-child hierarchies DAX pattern When the depth increases, new calculated columns must be added. This means that the depth of the hierarchy is fixed and limited to the number of calculated columns that was decided at design time. These functions are used to flatten the hierarchy as as set of calculated columns. This script is just a tweaked copy of the main loop in the original one Phil posted with a few small syntax changes as the Advanced Scripting appears to use a slightly older version of the c# compiler which does not have some of the newer features like interpolated strings.Implementing Parent-Child hierarchies in DAX requires the usage of a special set of functions, PATH, PATHITEM and PATHLENGTH, specifically designed for this purpose. Simply click run (1) and your new measures appear in your model (2) and once you save them they will appear in Power BI Desktop.īelow is the text version of the advanced script if you want to modify this to work in your environment. So with a simple 14 line script and no additional tools or downloads you can generate a set of data driven measures for your model. The advantage of these methods is that you don’t need to do any searching for port numbers or loading additional libraries and they make use of the existing authenticated connection, so there is no need to re-authenticate to the data model. These let you run DAX or TSML scripts directly against a connected data model. What this means is that you can just call ExecuteReader(query) instead of (query) This has now been fixed and Daniel has extended this method to return a DataSet (which can contain multiple DataTables) and he has also added the EvaluateDax method which can evaluate simple DAX expressions.Īlso these methods are also exposed as static methods. Update There was an issue with certain queries and the ExecuteDax method. As of Tabular Editor 2.12.0 the database object in Tabular Editor now has 3 new helper methods. Within an hour he actually came back with an even better approach which he had just added to Tabular Editor. The idea was that I should be able to do everything Phil did in his original script from the Tabular Editor Advanced Scripting feature. ![]() While I was writing my previous post I sent a question to Daniel who is the author of Tabular Editor, asking if there was a way of getting the connection details when Tabular Editor was launched from the External Tools. Which in turn was inspired by this excellent post by Phil Seamark. This is a follow-up to my previous post on using a combination of DAX Studio and Tabular Editor to generate a series of measures based on the values in a given column. ![]()
0 Comments
Leave a Reply. |