Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi guys,
I have created simplified schema of my dataset below. I would like to visualize:
1. Transactions per date
2. Customers per registration date
With current set up, I am unable to create relationships due to ambiguity. This model is very simple, in reality I have dozen of DIM tables (such as Vendors, Employees etc.) with other colums. Currently I have created copy of DIM Customer table however this is significantly increasing dataset size when doing the same for each DIM table. For some calculations, I used USERELATIONSHIP function but I would need to create dozen of measures which is definately not user friendly.
Is there any other way how to handle such relationships in dataset as described?
Thank you for any help
IvanS
Solved! Go to Solution.
Yeah, for sure. However, as you're wanting to aggregate customers by registration date.. that technically means it should be considered a fact table in a star schema.
So, you are correct in your OP. Your two options are to restructure your model, or add USERELATIONSHIP to your measures.
As you currently have dates in your DIM Customer table, I believe it technically makes it a FACT table. I think the proper star schema would be to have a separate DIM Customer table with only a distinct list of customer id's. Then you'll have a relationship from Date to FACT Transactions and to FACT Customer Dates.
Hi @CoreyP ,
the Customer table is dimensional as Customer ID is unique there and there is only one registered date assigned to unique Customer ID - example:
Customer ID | Name | Language | Registration date | City | Country |
1 | Johnny | en | 1.1.2024 | New York | US |
2 | Simon | fr | 20.12.2023 | Paris | France |
So creating distinct list of customer ID's will lead into having duplicate table of the same size (which is actually that I am doing right now) but I am looking for ways to reduce size of dataset.
Yeah, for sure. However, as you're wanting to aggregate customers by registration date.. that technically means it should be considered a fact table in a star schema.
So, you are correct in your OP. Your two options are to restructure your model, or add USERELATIONSHIP to your measures.
In your Customers dataset, make sure to have a unique row for each customer by let's say a Customer_ID and then you can have all other demograhic infomrmation about a customer in this table.
For Transactions table you can have a Customer_ID colun with all transaction details.
Join the two table on Customer_ID with 1 to many relationship.
Hope this helps.
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |