Measure to give value of parent in parent/child relationship

[DavidAtkins]

This is one of those questions that I can do quickly in Excel but I just can't get it to work in DAX!

I have two tables which I've simplified below (in reality they have a lot more columns and rows). The first is a list of parts showing where they are held and how many we hold. The second is a table of locations where each location has a "Parent" location.

Table1				Table2
Part Number	Location	Count		Location	Parent
1	A	10		A	A
1	B	15		B	A
1	C	7		C	A
2	D	68		D	B
2	E	20		E	B
3	F	34		F	C
3	G	84		G	C

What I want to do is create a measure in table2 that shows the count of the parent like below:

Location	Parent	Count (Measure)	Parent Count (Measure)
A	A	10	10
B	A	15	10
C	A	7	10
D	B	68	15
E	B	20	15
F	C	34	7
G	C	84	7

The first measure is simply Count (Measure) = SUM( Table1[Count] ).

I tried doing something like 

Parent Count (Measure) = CALCULATE( [Count (Measure)] , Table2[Location] == Table2[Parent] ) but that doesn't work.

In Excel I'd do this with a couple of sumifs but I've no idea how to do it in DAX. I'm using directquery if that makes a difference.

Thanks in advance for any help.

[Jack_D]

I can't attache my file as a new member. I think the relationship between Part number and Parent is missing.

If we can add a table( table 3) to creat ralationship between Parent and Part Number.
Part Number  Parent
1                    A
2                    B

3                    C

JMeasure =

CALCULATE(SUM('Table'[Count]),CROSSFILTER('Table 3'[Parent],'Table 2'[Parent],Both)

)

JMeasure2 =

var var1 = SELECTEDVALUE('Table 2'[Parent])

return

CALCULATE(MIN('Table'[Count]),all('Table'),filter(all('Table'),'Table'[Location] in {var1}))

[v-binbinyu-msft · Accepted solution]

Hi @DavidAtkins ,

Please try to create measure with below dax formula:

Measure =
VAR _a =
    SELECTEDVALUE ( 'Table 2'[Parent] )
VAR _b =
    SELECTEDVALUE ( 'Table 2'[Location] )
VAR _r1 =
    CALCULATE (
        SUM ( 'Table'[Count] ),
        FILTER ( ALL ( 'Table' ), [Location] IN { _a, _b } )
    )
RETURN
    _r1

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MohamadAshuryan]

Hi DavidAtkins

after creat relationship between table1,table2

use this calculated columns:

Count =
RELATED(
table1[count]
)

Parent Count =

LOOKUPVALUE(

    Table2[Count],

    Table2[Location],

    Table2[Parent]

    )

[DavidAtkins]

Thanks for this but I'm using directquery so can't use LOOKUPVALUE. Also the first table can have multiple parts in a location so I can't use RELATED on the Count column which is why I made a Count measure in table2.