DATATABLE Function DAX

DATATABLE function is a Power BI Table manipulation function in DAX which allows us to create a table.




Using Datatable function, You can define column and datatype for table and add values to them.

SYNTAX

DATATABLE

(column_Nname1, datatype1,

column_name2, datatype2..., 

{{value1, value2...}, {valueN, valueN+1 ..}...}

)

column_name is a Dax expression that returns a table.

datatype is a datatype of column it could be an INTEGER, DOUBLE, STRING, BOOLEAN, CURRENCY, DATETIME.

value is data values that will be in the table, missing value will be considered as BLANK().

Lets look at an example of using DATATABLE Function.

Creating a DATATABLE, go to modeling tab in ribbon bar then click on DAX expression to create a new table icon as shown below.

 

 

Lets create a table named StudentData using DATATABLE DAX.

Following DAX uses DATATABLE function, that create a table named StudentData which contains two column named StudId and StudName, and Insert two rows in table.

StudentData =
DATATABLE(
  "StudId", INTEGER,
  "StudName", STRING,
    {
      {1, "Rakesh Mehta"},
      {2, "Manoj Singh"},
      {3, "Subham Rawat"}
    }
    )

 

Once you commit the DAX, a table is created in Power Bi model.

You can also see the table’s output in Data view as shown below.

You can see, it returns two rows.

 

It is must to provide a value for all columns that you define in DATATABLE function otherwise you may get an error.

Lets modify above DAX, just remove the Student Name value from first row and commit the DAX,  you will see it returns an error as shown below.

StudentData =
DATATABLE(
 "StudId", INTEGER,
 "StudName", STRING,
  {
   {1},
   {2, "Manoj Singh"},
   {3, "Subham Rawat"}
   }
  )

 

Lets see, if you do not have value for any column then how would you deal with that as you can see in above example, we did not provide a value for Student Name in first row and it gives an error.

Lets do small change in DATATABLE function DAX just put the comma after providing a value for student id in first row.

So by doing this you have just matched the number of values with number of defined columns in table to avoid error.

StudentData =
DATATABLE(
 "StudId", INTEGER,
 "StudName", STRING,
 {
  {1, },
  {2, "Manoj Singh"},
  {3, "Subham Rawat"}
  }
  )

As you can see, this time it does not give error and returns blank value for student name.

Lets do more with this DATATABLE function, you can see it provides blank values in case if you do not provide values.

StudentData =
DATATABLE(
 "StudId", INTEGER,
 "StudName", STRING,
 {
  {1, },
  { , "Manoj Singh"},
  { ,  }
  }
 )

Table manipulation function

SUMMARIZE

SUMMARIZECOLUMNS

VALUES

 




SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More




 50 total views,  2 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.