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

[Madhumitha_V]

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 !!

[Madhumitha_V]

Thankyou in advance !!

[Icey · Accepted solution]

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 functions, SUMMARIZE(), 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]

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.