Tuesday, 12 December 2017

How to Create a New Calculated Table from Existing Table in Power BI

Creating a New Calculated Table from Existing Table in Power BI
We can Create a New Table with Single Column using the "DISTINCT" Function on Existing Table. Also, we can create a New Table with Multiple Columns using the "SUMMARIZE" Function on Existing Related Tables in Power BI.

1)Creating a New Table with Single Column from One Existing Table:
Suppose we have a Table "vSalesByCustRegion" with Multiple Fields as follows.



From this Table, we wants to extract the Distinct Product Names into a New Table called "Tbl_DistProdNames".

A)Go to Power BI Report > Modeling Tab > New Table
Click on the New Table 



B)Next Enter the DAX Expression/Formula to Create the Table
Enter the below DAX Formula in the Formula Bar

Tbl_DistProdNames = DISTINCT(vSalesByCustRegion[Prod_Name])



Next press Enter Key , it will Create a New Table Tbl_DistProdNames with One Field "Prod_Name"

Result :



2)Creating a New Table with Multiple Columns from One Existing Table :
Here want to Create a New Table "Tbl_CustDetails" with Multiple Columns from an Existing Table.

Enter the below DAX Formula in the Formula Bar

Tbl_CustDetails = SUMMARIZE(vSalesByCustRegion,vSalesByCustRegion[Cust_Name],vSalesByCustRegion[Cust_Segment],vSalesByCustRegion[Region_Name])

Result :

3)Creating a New Table with Multiple Columns from Multiple Related Tables :

Here want to Create a New Table "Tbl_CustProdSup" with Multiple Columns from an Existing Related Table.
Please note when we are creating a New Table from Multiple Tables, there should be a defined relation between them.

Here in my Data Model, I have one Fact Table and 3 Dimension Tables , which have already a Relation between them.


When we are Creating a New Table using Summarize Formula , Once we enter the Summarization Table Name (here the FactSales Table ) it will show the Related Table Names and their Fields that we can use for Group By.


Enter the below DAX Formula in the Formula Bar

Tbl_CustDetails = SUMMARIZE(vSalesByCustRegion,vSalesByCustRegion[Cust_Name],vSalesByCustRegion[Cust_Segment],vSalesByCustRegion[Region_Name])

Result :


--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence Professional
--------------------------------------------------------------------------------------------------------

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts