SUM values in fact table, filtering by keys in dim table

[Mastoda]

Hi,

I have a DIM_TABLE with groups and subgroups of keys

DIM_TABLE:

and a FACT_TABLE with transactions values
FACT_TABLE:

I want to perform calculation in fact table based in KEY_GROUP

CALCULATION_A = KEY_GROUP 1 + KEY_GROUP 3

CALCULATION_A = 10+5+2 = 17

there's some easy way to do this?

[v-chuncz-msft · Accepted solution]

@Mastoda,

You may refer to measure below.

Measure =
SUMX (
    FILTER (
        FACT_TABLE,
        LOOKUPVALUE (
            DIM_TABLE[KEY_GROUP],
            DIM_TABLE[KEY_SUBGROUP], FACT_TABLE[KEY_SUBGROUP]
        )
            IN { 1, 3 }
    ),
    FACT_TABLE[VALUE]
)

[Mastoda]

Thank you!