Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to Solution.
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
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
@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.
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
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!
User | Count |
---|---|
77 | |
76 | |
41 | |
29 | |
24 |
User | Count |
---|---|
96 | |
91 | |
52 | |
47 | |
46 |