cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ask4atish
Advocate III
Advocate III

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:

 

Sourcenoamount
A112
A285
A361
A492
A511
B165
B225
B371
B431
B515


Table 2:

Sourcenocustomername
A1A
A2B
A3C
A4D
A5E


Table 3

Sourcenovendor
B1V1
B2V2
B3V3
B4V4
B5V5


Expected output should be like mentioned below:

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



Please help me in achieving the desired output.

9 REPLIES 9
v-gizhi-msft
Community Support
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:

20.PNG

Here is my test pbix file:

pbix 

Hope this helps.

 

Best Regards,

Giotto

karun_r
Microsoft
Microsoft

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.

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

vivran22
Super User I
Super User I

Hello @ask4atish ,

 

You may use Power Query for getting the desired output:

 

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

1.PNG

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

2.PNG

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

3.PNG

  • Remove SourceNo column

4.PNG

 

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/

Connect on LinkedIn

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.

Mariusz
Super User II
Super User II

Hi @ask4atish 

 

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.
LinkedIn

 

 

 

Hi @Mariusz ,

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

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors