How to add calculated column using DAX in Power Pivot

New DAX (Data Analysis Expression) Language is used to create calculated columns in Microsoft's Power Pivot. DAX formulas is compromised of an equal sign followed by a function or expression.Once the formula is created it is applied to the entire column.Column can only be recalculated if underlying data is refreshed or if manual recalculation is done.

I want to create a calculated column called Full_Name based on 3 other columns First_Name, Middle_Name and Last_Name.Concept here is very similar calucated columns using SSAS Data Source View, except here we use Microsoft's new DAX expresssion.In this example I also want to add condition such that if the middle name is empty then exclude else inculde Middlename.

I have a simple customer table which looks like this:



Next step is to fire Excel Instance with Power Pivot plug-ins and connect to SQL Database (in my case BlogDB).




Click Next button, now wizard prompts connect to SQL table.



Next screen choose table we can to import, in our case it is CustomerName table.



Now Click finish




Now you have successfully created a power pivot table. Next Step is create a calculated column based on FistName,LastName and MiddleName.

Open up power pivot table focus on a column where you want to apply DAX expression. Enter the following expression.

=CONCATENATE( [FirstName]&" ", CONCATENATE( IF( LEN([MiddleName])>1, LEFT([MiddleName],1)&" ", ""), [LastName]))



Once you execute the expression result looks like this.



Notice how middle name is skipped for Mary Peterson.Now we have created a calculated columns in Power Pivot table.Pivot table model can be consumed other excel sheet or linked with other Power Pivot models.