Forum Discussion
Helper VSummarize 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.
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.
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
4 Replies
- Jihwan_Kim
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.
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
YuiitsuThank 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!
- TomMartens
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
- Yuiitsu
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.
