Missing Key in Fact table

[Stabben23]

Hi, my first post in this community.

I have One Fact and one dimension table connected one to many in a both direction. I have all keys in my dimenssion but missing some of them in my fact. 

The problem occurs when I want to use a measure from Dimension table, se pictures. 

The problem here is that nothing have been produced this day on Machine E3 so Key value 2019-06-23-E3 is not found in Fact.

I want to use measures from fact and dimension in same visualizations.

So my question is; how can I get 48 Hours on this day? 

[v-juanli-msft]

Hi @Stabben23 

This is my exmaple pbix, you could download and check if my solution would help.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft · Accepted solution]

Hi @Stabben23 

Assume you have tables

Create columns in dimension table

rank index = RANKX(FILTER('dimension','dimension'[date]=EARLIER('dimension'[date])),[extruder.1],,ASC,Dense)

day goal 2 =
VAR goal1 =
    CALCULATE (
        SUM ( 'fact'[day goal] ),
        FILTER (
            'dimension',
            'dimension'[key_to_oee.1] = EARLIER ( 'dimension'[key_to_oee.1] )
        )
    )
RETURN
    IF (
        goal1 = BLANK (),
        0.5
            * SUMX (
                FILTER (
                    'dimension',
                    'dimension'[date] = EARLIER ( 'dimension'[date] )
                        && (
                            'dimension'[rank index]
                                = EARLIER ( 'dimension'[rank index] ) - 1
                                || 'dimension'[rank index]
                                    = EARLIER ( 'dimension'[rank index] ) + 1
                        )
                ),
                CALCULATE (
                    SUM ( 'fact'[day goal] ),
                    FILTER (
                        'dimension',
                        'dimension'[key_to_oee.1] = EARLIER ( 'dimension'[key_to_oee.1] )
                    )
                )
            ),
        goal1
    )

hours 2 =
VAR hour1 =
    CALCULATE (
        SUM ( 'fact'[hours] ),
        FILTER (
            'dimension',
            'dimension'[key_to_oee.1] = EARLIER ( 'dimension'[key_to_oee.1] )
        )
    )
RETURN
    IF (
        hour1 = BLANK (),
        0.5
            * SUMX (
                FILTER (
                    'dimension',
                    'dimension'[date] = EARLIER ( 'dimension'[date] )
                        && (
                            'dimension'[rank index]
                                = EARLIER ( 'dimension'[rank index] ) - 1
                                || 'dimension'[rank index]
                                    = EARLIER ( 'dimension'[rank index] ) + 1
                        )
                ),
                CALCULATE (
                    SUM ( 'fact'[hours] ),
                    FILTER (
                        'dimension',
                        'dimension'[key_to_oee.1] = EARLIER ( 'dimension'[key_to_oee.1] )
                    )
                )
            ),
        hour1
    )

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.