Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Madhumitha_V
Helper II
Helper II

How to Create a New table by pulling in the required columns from 2 or more tables?

Hi All,

 

The source for my report is SQL Database. I have 3 tables namely - Users, Application & Price. There is a relationship between Application Table & Price Table and Application Table & Users Table. But we dont have any relationship between Users table to Price Table. I have placed a KPI Box to display the sum of Price, which will come from the Price Table. 

 

Email from User Table is used as slicer to slice the data for each individual users. Since we have dont have any relationship(No common fields) between Users and Price Table, we cannot able to slice the data. But my ultimate goal is to slice the Total Price in the KPI Box according to the Email Slicer selection. How can we achieve that ? 

 

I have an idea of placing the fields Email, Price, Application Name and required fields to slice in the New Calculated table and then slice the data using Email in this table. Will that work ? 

 

Basically I wanted to know

1 - How can we create a New (Calculated) Table in Power BI by pulling in the required columns from 3 different tables ? And what is the syntax for that ?

2 - How can we achieve the Data sciling when we have no direct relationships between those 2 tables ? Any DAX functions to achieve this data sciling?

 

Please share your thoughts on this.

Any pointers on this would be of great help!

 

Thankyou in advance !!

 

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

Hi @Madhumitha_V ,

 


 

1 - How can we create a New (Calculated) Table in Power BI by pulling in the required columns from 3 different tables ? And what is the syntax for that ?

 


Try to use some table manipulation functionsSUMMARIZE(), SELECTCOLUMNS(), ADDCOLUMNS(), like so:

//Solution1
New Table =
UNION (
    SUMMARIZE ( Table1, [Group_by_Me], "Column1", [Column1] ),
    SUMMARIZE ( Table2, [Group_by_Me], "Column2", [Column2] ),
    SUMMARIZE ( Table3, [Group_by_Me], "Column3", [Column3] ),
    SUMMARIZE ( Table4, [Group_by_Me], "Column4", [Column4] )
)
//Solution2
New Table =
UNION (
    SELECTCOLUMNS ( Table1, "Column1", [Column1] ),
    SELECTCOLUMNS ( Table2, "Column2", [Column2] ),
    SELECTCOLUMNS ( Table3, "Column3", [Column3] ),
    SELECTCOLUMNS ( Table4, "Column4", [Column4] )
)

 

 


 

2 - How can we achieve the Data sciling when we have no direct relationships between those 2 tables ? Any DAX functions to achieve this data sciling?

 

 

It is suggested to use bi-directional relationships, if there are only a few tables. 

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Icey
Community Support
Community Support

Hi @Madhumitha_V ,

 


 

1 - How can we create a New (Calculated) Table in Power BI by pulling in the required columns from 3 different tables ? And what is the syntax for that ?

 


Try to use some table manipulation functionsSUMMARIZE(), SELECTCOLUMNS(), ADDCOLUMNS(), like so:

//Solution1
New Table =
UNION (
    SUMMARIZE ( Table1, [Group_by_Me], "Column1", [Column1] ),
    SUMMARIZE ( Table2, [Group_by_Me], "Column2", [Column2] ),
    SUMMARIZE ( Table3, [Group_by_Me], "Column3", [Column3] ),
    SUMMARIZE ( Table4, [Group_by_Me], "Column4", [Column4] )
)
//Solution2
New Table =
UNION (
    SELECTCOLUMNS ( Table1, "Column1", [Column1] ),
    SELECTCOLUMNS ( Table2, "Column2", [Column2] ),
    SELECTCOLUMNS ( Table3, "Column3", [Column3] ),
    SELECTCOLUMNS ( Table4, "Column4", [Column4] )
)

 

 


 

2 - How can we achieve the Data sciling when we have no direct relationships between those 2 tables ? Any DAX functions to achieve this data sciling?

 

 

It is suggested to use bi-directional relationships, if there are only a few tables. 

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BI_Jo
Resolver III
Resolver III

Hi

 

You could you use the 'merge queries as new' option in query editor to create a new table that has values from all 3 tables.

What type of relationship is there between Users & Application tables and Application & Price tables (many to one, many to many)?  Knowing that may help me figure out what would work.

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.