Tuesday, December 12, 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_CustProdSup= SUMMARIZE(FactSales,DimCustomers[CustName],DimProducts[Prod_Name],DimSuppliers[Supplier_Name])

Result :

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

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog