DAX: Accessing values of parents? (using junction table)

[Anonymous]

Hi, sorry trying to make this work and decompressed my real-life problem into this simple case:

 

Essentially, I want to add a measure column to the `stores` table.
This `measure` column would find all the parents the child has and combine (sum or average) all its `some_value`'s.

I was trying to do it in a way like this

Measure = CALCULATE(SUM(stores[some_value]), FILTER(ALL(stores), 'stores'[store_child_and_parent] = VALUES(store_relationship[store_parent])))

but I feel lost. 

[Greg_Deckler]

So, in the data from your example, 5 would get a value of 666 (if summing) and 4 would be 111, correct?

[Anonymous]

Ah, sorry didn't mention it in the post. You are correct! 666 and 111!

[Zubair_Muhammad]

@Anonymous

Please try this MEASURE

Measure =
VAR parents =
    VALUES ( store_relationship[store_parent] )
VAR result =
    CALCULATE (
        SUM ( Stores[some_value] ),
        FILTER ( ALL ( Stores ), Stores[store_child_and_parent] IN parents )
    )
RETURN
    IF ( ISBLANK ( result ), 0, result )

[Zubair_Muhammad]

@Anonymous

Please see attached file with your sample data

[Anonymous · Accepted solution]

Thanks, @Zubair_Muhammad!

Your solution kickstarted my brain and eventually I managed to figure out what I wanted!

measure = CALCULATE(
    SUM(
        'stores'[some_value]
    ),
    FILTER(
        ALL('stores'),
        'stores'[store_child_and_parent] in VALUES('store_relationship'[store_parent])
    )
)

But then, because some_value (albeit not in this example but in my real world one) is actually a measure I was looking to get an average grouped by the child store.... was about to give up and ask here but eventually I did it!!!

measure = CALCULATE(
    AVERAGEX(
        'stores',
        'stores'[some_value]
    ),
    FILTER(
        ALL('stores'),
        'stores'[store_child_and_parent] in VALUES('store_relationship'[store_parent])
    )
)

I'm the happiest DAX newbie in the world right now!

Still trying to figure out this whole DAX thing, it seems very powerful but not as obvious.