Forum Discussion
Fundamental Data Model Relationship Question
- This measure ignores (ALLEXCEPT) the relation and creates foreach combination a result.
mwegener , Anonymous
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.
- 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.
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.
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.