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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Yuiitsu
Helper V
Helper V

Summarize 1 particular column across multiple tables

Dear Everyone

 

I have a question but I cant find the exact solution to my problem anywhere in this forum.

 

My data have 4 facts table: Budget, Forecast, Booking, Customer relationship

All these 4 facts table have a common column call "Customer name"

So I want to create a table with unique Customer_Key by summarizing the column "Customer name" from all 4 facts table.

This is so that I can connect them in the Star schema with Many to One relationship.

 

The 4 files are quite big in nature so I need a method that will not freeze my powerbi desktop when loading.

If there are other better work around please show me how I can do this.

 

Thank you.

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi, 

I am not sure how your source tables looks like, but I tried to create a sample pbix file like below.

One of the best ways is to ask data foundation team to create a customer dimension table. However, if it is not possible, then please try creating in power query editor.

please check the below picture and the attached pbix file.

 

Jihwan_Kim_0-1722831874329.png

 

The M code looks like below in Advanced Editor

let
    Source = Table.Distinct ( Table.Combine({ Table.SelectColumns(budget,{"customer_name"}) , Table.SelectColumns(forecast,{"customer_name"}), Table.SelectColumns(booking,{"customer_name"}), Table.SelectColumns(relationship,{"customer_name"})}) )
in
    Source

 

Jihwan_Kim_1-1722831972742.png

 


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

View solution in original post

4 REPLIES 4
TomMartens
Super User
Super User

Hey @Yuiitsu ,

 

in addition to the advise of @Jihwan_Kim creating a customer dimension table, you can consider to separate the content from the data as outlined in this article: Separate reports from models in Power BI Desktop - Power BI | Microsoft Learn

 

As soon as you only work on data visualizations your local machine will not be blocked by a data refresh when the data refresh/load is performed by the Power BI Service.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

@TomMartens Thank you for suggesting an alternative!

I am not familiar with seperating reports from models in PowerBI desktop but it is something I would love to explore and learn. 

Surely I will read and try out this method too.

Jihwan_Kim
Super User
Super User

Hi, 

I am not sure how your source tables looks like, but I tried to create a sample pbix file like below.

One of the best ways is to ask data foundation team to create a customer dimension table. However, if it is not possible, then please try creating in power query editor.

please check the below picture and the attached pbix file.

 

Jihwan_Kim_0-1722831874329.png

 

The M code looks like below in Advanced Editor

let
    Source = Table.Distinct ( Table.Combine({ Table.SelectColumns(budget,{"customer_name"}) , Table.SelectColumns(forecast,{"customer_name"}), Table.SelectColumns(booking,{"customer_name"}), Table.SelectColumns(relationship,{"customer_name"})}) )
in
    Source

 

Jihwan_Kim_1-1722831972742.png

 


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

Thank you @Jihwan_Kim 

I tried your method and it works!!

So far I do not find any loading issue, hope it will not get laggy when the files gets bigger in the future!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.