Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I've created a data model with a Fact table and Dim table. I've also create a relationship(1:M) from the Dim to the Fact table. Now, when a start creating a Table Visual for my "spot-checking" needs and bring in the Key fields from both tables that I created my relationship based on, the amount of records are inflated. I would think I would only see records in the Table Visual where the Key fields match between the Fact table and Dim table but it seems as if a cross-join of some sort is happening behind the scenes but I think I'm missing something fundamental here in my understanding.
As you can see the screenshot, the 2nd Key field(From Dim table) is being replicated when the 1st Key field(From Fact table) in the screenshot is unique as I build out my Table Visual. I'm expecting to see only records where the two Key fields match. However, it seems like all records from the Dim Table will "cross join" to each record in the Fact table based on the relationship created between the tables. The Key field from the Dim table is unique and I created a 1:M relationship to the Fact table.
Any explanation would be helpful.
Solved! Go to Solution.
HI @Anonymous ,
It seems like table records not fully matched, right? If this is a case, I'd like to suggest you create a calculated table that extracts two table key fields, then you can sue it as bridge to link two tables.
BTW, I'm not so clear for your data structure, can you please share some sample data to help us clarify your scenario and test?
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Hi @Anonymous ,
could you post a screenshot of your data model view?
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
Here is the data model screenshot. I'll also add that there will be some cases that there's no Key field value from the Dim table that matches the Key field value in the Fact table. All the Key field values from the Dim table will find a matching Key field value in the Fact table, just not the other way around.
Here is an example of my Table Visual. There are more records to the left that you can't see but those are descriptive fields. You can see in the screenshot the correct record with some more fields populated with values. The relationship between the Fact and Dim and bringining in the Schedule Dim Key into the Table Visual is causing the record to then explode and repeat as many times as there are Dim records.
Here's another screenshot with "Use in COS OEE Calc" and "Plant Schedule Dim Key" removed from the Table Visual. The only two Dim fields that remain are "FY" and "Fiscal Month Name" on the left-hand side. The rest of the fields are from the Fact table. You can see that when I removed the two fields that the Table Visual records shrink down. Is it because of the incomplete matches between the Fact table and Dim table that causes this sort of behavior? What I mean is that there are some Key field values in the Fact table that won't find a corresponding match in the Dim table.
I removed the field "Production Qty (Date-PlantNum-PlantType-ProdLine-Shift)" from the Table Visual and that shrunk the records down to one. Why would this particular field I removed cause the records to repeat in the Table Visual? I would expect I could add this field to the Table Visual without experiencing any odd behaviors.
@mwegener Thank you for your explanantion behind this behavior I was seeing with my Table Visual.
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |