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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Fundamental Data Model Relationship Question

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.

 

FACTvsDIM-DimKeyFieldReplicatedinTableViz.PNG

 

Any explanation would be helpful.

1 ACCEPTED SOLUTION

This measure ignores (ALLEXCEPT) the relation and creates foreach combination a result.
Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


View solution in original post

13 REPLIES 13
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Anonymous
Not applicable

@mwegener , @v-shex-msft 

 

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.

 

DataModelCOSOEE.PNG

Is there any Measure in your Table Visual?
Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Anonymous
Not applicable

@mwegener , @v-shex-msft 

 

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.

 

TableVisual-FACTvsDIM-RelationshipKeys.PNG

Could you post the DAX statements from the Measure "Production Qty ( Date-....)" and "Use in COS OEE Calc"?
Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Anonymous
Not applicable

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.

 

TableVisual-FACTvsDIM-RelationshipKeys-SomeDimFieldRemoved.PNG

Anonymous
Not applicable

There's DAX formula behind the "Production Qty..." field but there isn't for the "Use in COS OEE Calc". The "Use in COS OEE Calc" is a field that contains either 1 or 0 that is already set and being pulled from the Plant Schedule Dim table into the Table Visual. 
 
Production Qty (Date-PlantNum-PlantType-ProdLine-Shift) = CALCULATE(SUMX('HANA Production Fact','HANA Production Fact'[Production Quantity]),ALLEXCEPT('HANA Production Fact','HANA Production Fact'[Production Date], 'HANA Production Fact'[Plant], 'HANA Production Fact'[Plant Type],'HANA Production Fact'[Production Line and Line Type], 'HANA Production Fact'[Shift]))

Make a fast try and remove the Measure "Production Qty (Date-PlantNum-PlantType-ProdLine-Shift)" from the table visual.
Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Anonymous
Not applicable

@mwegener 

 

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. 

This measure ignores (ALLEXCEPT) the relation and creates foreach combination a result.
Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Anonymous
Not applicable

@mwegener  Thank you for your explanantion behind this behavior I was seeing with my Table Visual.

Anonymous
Not applicable

@mwegener 

 

Yes there are measures that I'm bringing into my Table Visual.

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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