cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Summarize three table fields in one table

Hi,

I want to create a table visual where data is coming from three different tables which are linked with each other on a key.

Table 1:

 Sourceno amount A1 12 A2 85 A3 61 A4 92 A5 11 B1 65 B2 25 B3 71 B4 31 B5 15

Table 2:

 Sourceno customername A1 A A2 B A3 C A4 D A5 E

Table 3

 Sourceno vendor B1 V1 B2 V2 B3 V3 B4 V4 B5 V5

Expected output should be like mentioned below:

 Amount customer/vendor 12 A 85 B 61 C 92 D 11 E 65 V1 25 V2 71 V3 31 V4 15 V5

9 REPLIES 9
Community Support

Hi,

Please try to create a calculated table as below:

``````Table =
UNION (
SELECTCOLUMNS (
'Table 2',
"customer/vendor", 'Table 2'[customername],
"Amount", RELATED ( 'Table 1'[amount] )
),
SELECTCOLUMNS (
'Table 3',
"customer/vendor", 'Table 3'[vendor],
"Amount", RELATED ( 'Table 1'[amount] )
)
)``````

The result shows:

Here is my test pbix file:

Hope this helps.

Best Regards,

Giotto

Employee

As suggested by the other poster, you can append both Table2 and Table3 into one single "dimension" table and establish a 1:N relationship between the appended table and the "fact" table which has got the Amount

Then you can use RELATED() function with ISBLANK() to create a calculated column that will check if the source has a corresponding customer and if it doesn't it will pull up vendor. So we are looking at two RELATED() calls.

Keep in mind that you can do this operation without appending the tables and it would work just fine. But probably the real question would be how many rows are we looking at here in the "fact" table with "Amount" column. It might not be a good idea to create a calculated column there, especially with two RELATED() calls.

Hi @karun_r ,

Rows are in millions. As suggested by you, this will not be a good idea to create a calculated column there, especially with two RELATED() calls.

Employee

Is it possible to do the operation equivalent to RELATED during your query pull ? If your fact table has millions of rows, what is the source from which you are pulling it from ? If it's a SQL Server or any relational DB, it might be wise to do the operation there instead of thinking of ways to do in DAX. However, please let us know if you find any suitable solution for this without RELATED()

@karun_r My data source is MS SQL. I will now perform the operation in SQL

Community Champion

You may use Power Query for getting the desired output:

• Merge Table 1 with Table 2 on Source no. and extract Customer Name

• Merge the result table above with Table 3 on Source no. and extract Vendor

• Comine the two columns table2.CustomerName and table3.Vendor

• Remove SourceNo column

You may find sample pbix file here

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Hi @vivran22 ,

This i snot working in my case. Related function is not picking the tables to choose from. My tables are linked with 1:N and yours are 1:1.

Community Champion

You can append Table 2 and Table 3 in Power Query, but before you will need to rename both customer and vendor columns to  customer-vendor

Or use Union Dax Function

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

Hi @Mariusz ,

I followed the steps provided by you. However, now amount field is showing blank or 0 whenever I perform sum on this.

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors