Forum Discussion

Anonymous

Not applicable

6 years ago

Solved

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-check...

mwegener
6 years ago
This measure ignores (ALLEXCEPT) the relation and creates foreach combination a result.

Anonymous

Not applicable

6 years ago

mwegener , Anonymous

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.

Most Valuable Professional

6 years ago

Is there any Measure in your Table Visual?

Anonymous
Not applicable
6 years ago
mwegener

Yes there are measures that I'm bringing into my Table Visual.
Anonymous
Not applicable
6 years ago
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.
- mwegener
  Most Valuable Professional
  6 years ago
  Could you post the DAX statements from the Measure "Production Qty ( Date-....)" and "Use in COS OEE Calc"?
  - Anonymous
    Not applicable
    6 years ago
    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]))