cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
R3qu1r3m3nt5
Frequent Visitor

Is it possible to use a DAX table column (list/rows) to generate DAX tables

Hi Folks,

 

After searching for an answer for longer than I care to admit I would really appreciate some guidance.  I am using power query to read an excel sheet that contains data items that are transformed into DAX calculatetable sytntax which I would like to use to build required calculatedtable(s).  Is it possible to use the formatted DAX syntax (column/list)  to create required tables? I have searched for a solution but I am now beginning to wonder if my approach is just flawed or not possible...

Raw Timesheet Items columns read xlsx and transformed by Power Query – 

R3qu1r3m3nt5_1-1605100449433.png

 
 

Result table containing Formatted DAX Syntax for calculatedtable -

R3qu1r3m3nt5_4-1605102232580.png

Can each row in the column DAX_Tables above be read/passed to create all the filtered tables (DAX code syntax lines that will query/filter a timesheet DB/easydata data store)? Then in theory, if I need a new filtered table I just add the required row (details) to my excel sheet (Timesheet Items read) and refresh my report to create any new calculatetables?

Also, if I need to add a column (Addcolumns) to the calculatedtable(s) , what is the syntax/is this also possible?

Thanks in advance.

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Hi Fowmy,

Many thanks taking the time to reply and suggesting the Tabular Editor external tool this is, a great tool, I can now add new calculatetable code without the model refreshing after each new table is added and the ability to bulk update/change the model so quickly is just magic (as are all the other functions now available).  

What would also be useful is a method to read/import a file containing rows of DAX code/syntax (in my case calculatetable DAX syntax please see screen 2nd shot from original post) adding new tables to the model reading in each row to end of file.  I don’t think Tabluar Editor could reference/read/import the DAX code/syntax file from the query editor output (as the Tabular Editor is working with the model metadata not actual data) but, maybe DAX code could be imported/read using an external file (XLSX/CSV/TXT).  I have looked at the Tabular Editor documentation specifically, import tables wizard functions (and Tabular Editor scripting tool/looping functions) but I am unable to see how to achieve this at the meoment. 

Your advice is welcome.

View solution in original post

3 REPLIES 3
Fowmy
Super User IV
Super User IV

@R3qu1r3m3nt5 

It should be possible using the Tabular Editor Under the External Tools. Please refer to these articles and expand on them.

https://medium.com/@elias.nordlinder/why-tabular-editor-is-a-must-for-power-bi-developers-cd5c2be62c...

https://powerbi.tips/2020/04/tabular-editor-rocks/

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn

Hi Fowmy,

Many thanks taking the time to reply and suggesting the Tabular Editor external tool this is, a great tool, I can now add new calculatetable code without the model refreshing after each new table is added and the ability to bulk update/change the model so quickly is just magic (as are all the other functions now available).  

What would also be useful is a method to read/import a file containing rows of DAX code/syntax (in my case calculatetable DAX syntax please see screen 2nd shot from original post) adding new tables to the model reading in each row to end of file.  I don’t think Tabluar Editor could reference/read/import the DAX code/syntax file from the query editor output (as the Tabular Editor is working with the model metadata not actual data) but, maybe DAX code could be imported/read using an external file (XLSX/CSV/TXT).  I have looked at the Tabular Editor documentation specifically, import tables wizard functions (and Tabular Editor scripting tool/looping functions) but I am unable to see how to achieve this at the meoment. 

Your advice is welcome.

View solution in original post

Hi Fowmy,

 

Many thanks for suggesting Tabular Editor, Daniel Otykier was extremely helpful and I have used his script to modify my Power BI model and add multiple calculated table definitions instantly when running the script (using my input file) in Tabular Editor, I have pasted the link to Daniels fix reply below as it resolved my problem, and Tabular Editor offers so much more functionality.

https://github.com/otykier/TabularEditor/issues/704 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors