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 :
Result :
3) Creating a New Table with Multiple Columns from Multiple Related Tables :
Enter the below DAX Formula in the Formula Bar
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])
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.
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.