Forum Discussion

Stabben23's avatar
Stabben23
Helper I
6 years ago
Solved

Missing Key in Fact table

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 pr...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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.
v-juanli-msft's avatar
v-juanli-msft
Community Support
6 years ago

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.