Create Static Tables in DAX Using the DATATABLE Function

The DATATABLE function can create static tables in a Power BI model specifying column names and data types. Article and download: sql.bi/27271/?...
How to learn DAX: www.sqlbi.com/...
The definitive guide to DAX: www.sqlbi.com/...

[@ariarc]

It's been more than two years since this video was made and posted, and yet I haven't seen anyone explain static tables in DAX so clearly and thoroughtly. And believe me, I have looked far and wide. Thank you.

[@CJ-jc8tn]

Thank you Alberto for sharing different ways to create static tables. I wasn't aware of the DATATABLE function and will definitely use this approach going forward. Another SQLBI video, another nugget of knowledge learned !

[@davidirias3459]

You really are the master of masters in POWERBI!!!! Ver helpful.

[@stevemorrison9144]

You have been really great for a novice user ..many thanks

[@ThepExcel]

Thx for so many choices explained

[@advent7324]

Excellent info on creating tables. Thanks

[@jaimeildefonsosegurapena7328]

You could also add a M expression:
= Table.FromRows(
{
{"LOW", 0, 000},
{"MEDIUM", 100, 1000},
{"HIGH", 1000, 99999}
},
type table [#"Price Range" = text, #"Min Price" = number, #"Max Price" = number]
)

[@SQLBI]

Good point - you have to open the M editor to do that, you cannot use a calculated table in that case.

[@joaovasconcelos5360]

Excellent!

[@OnoDus]

Thank you Alberto! Great content

[@manjun1892]

Really helpful content.Thanks Alberto.

[@gondebas]

By your leave, master Alberto, you can give names to columns without SELECTCOLUMNS, as simple as that:
DATATABLE (
"RangeName", STRING,
"Price_Min", INTEGER,
"Price_Max", INTEGER,
{
{ "Low" , 1 , 10 },
{ "Midium" , 10 , 10 },
{ "High" , 100 , 1000 }
}
)

[@SQLBI]

The example with SELECTCOLUMNS uses another syntax - table constructor - which create a table using dynamic expressions (not allowed in DATATABLE) but cannot control the column name directly.

[@kalinduSekara]

Simple but very useful

[@braveheart3917]

Nicely explained

[@pmsocho]

Thanks for the video!

[@advrohitowhal9794]

THANKS !

[@papachoudhary5482]

Thanks! Sir

[@Phoenixspin]

Thanks, great information.

[@Bharath_PBI]

Thank you 👍

[@BryanBonjour]

thanks

[@davidtawa8338]

Thxs!!

[@RM-wz2ug]

Hello Alberto and thank you for sharing so much of your knowledge. I am relatively new to PBI but have learned a great deal from your videos. I have a challenge for which I can find no solution and wondering if you have ideas. I find myself with an inventory source that does not have a date/time stamp. The data is simply current data based on the latest transactions from the source system so I don't believe I can use incremental refresh. I need to figure out how to create a snap shot of this data each Friday. I am refreshing the data daily and trying to find a way to capture and keep a snapshot with the Friday Date/Time stamps so that I start building a history of inventory as of Friday's each week in a single table. The intent is to create visuals (eventually) that compares current inventory to year ago, month ago, etc. Ideas?

[@SQLBI]

There are examples of similar scenarios in our book: www.sqlbi.com/books/analyzing-data-with-microsoft-power-bi-and-power-pivot-for-excel/
And course: www.sqlbi.com/p/data-modeling-for-power-bi-video-course/

[@amarnadhgunakala2901]

That's cool stuff

[@MrSimp75]

Hello Alberto- How can one create user defined functions in SSAS cube to be used in DAX query. Something like inbuilt function like DATEVALUE() etc.

[@JS-ts2vv]

Thank you Sir, I was trying using this way as part of the Selector value to switch between two different measures using two different time attribute (activating by userelationship) to dimdate table. however, it doesnt work as plan. I have tried both using IF and SWITCH with no luck. the scenario is to have a chart that can show volumes either by Fiscal Year or Standard Year base on the selector. Would really appreciate your advise here and thanks in advance for the support!

[@rokiczaaa]

How to do similar table but with measures? I have to combine some measures to create a chart

[@ashishmohan4707]

Hi
very useful video and very practical need everyone for every project ......very big thanks to share with us.
If it can possible can you plzz provide us excel data file which you used.
Very greatful for us...👌👌👌👌👌👌👌👌💐💐💐💐🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂

[@SQLBI]

You can download the sample files following the link in the video description.

[@BernatAgulloRosello]

What about loading an excel file? You just need to modify the excel and refresh the table 😊 and you can create calculated columns (like a dynamic max value) workout trouble

[@BernatAgulloRosello]

Then again, not as instant as a dax expression I know, and if a new column appears it breaks everything. Plus and cons as usual

[@tameraziz2104]

Thank you.. Alberto, I have a real case which requires me to create a dynamic table coming from measures.
Any possible way?

[@SQLBI]

A measure can return only a scalar value (string, number). You can create tables in variables within the DAX expression of a measure, but you cannot return them from a measure.

[@Alpacastan21m]

How can we use data table but use measures and dynamic formulas instead of static data?

[@SQLBI]

Use the table constructor instead: dax.guide/op/table-constructor/
Then use SELECTCOLUMNS to control column names.