Filter on a calculated measure

Hi all, I've made a power pivot. See the picture below. I'm checking each article, what we have received from a distribution centre (DC) and what we have filled (EFC) and if we have made correctio...

Need Help

v-alq-msft

6 years ago

Hi, Anonymous

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create two measures as below.

Result = 
IF(
    ISINSCOPE('Table'[Level2]),
    SUM('Table'[Value]),
    var dc =
    CALCULATE(
        SUM('Table'[Value]),
        FILTER(
            ALL('Table'),
            'Table'[Level1]=MAX('Table'[Level1])&&
            'Table'[Level2]="DC"
        )
    )
    var efc =
    CALCULATE(
        SUM('Table'[Value]),
        FILTER(
            ALL('Table'),
            'Table'[Level1]=MAX('Table'[Level1])&&
            'Table'[Level2]="EFC"
        )
    )
    var _0318 =
    CALCULATE(
        SUM('Table'[Value]),
        FILTER(
            ALL('Table'),
            'Table'[Level1]=MAX('Table'[Level1])&&
            'Table'[Level2]="0318"
        )
    )
    return
    dc-efc-_0318
)

Visual Control = 
var dc =
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        ALL('Table'),
        'Table'[Level1]=MAX('Table'[Level1])&&
        'Table'[Level2]="DC"
    )
)
var efc =
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        ALL('Table'),
        'Table'[Level1]=MAX('Table'[Level1])&&
        'Table'[Level2]="EFC"
    )
)
var _0318 =
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        ALL('Table'),
        'Table'[Level1]=MAX('Table'[Level1])&&
        'Table'[Level2]="0318"
    )
)
return
IF(
    dc-efc-_0318=0,
    0,
    1
)

Finally you need to put 'Visual Control' in the visual level filter to display the result.