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.
