topic Re: Measure to give value of parent in parent/child relationship in DAX Commands and Tips

Measure to give value of parent in parent/child relationship

DavidAtkins — Fri, 08 Dec 2023 15:43:46 GMT

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.

Re: Measure to give value of parent in parent/child relationship

MohamadAshuryan — Fri, 08 Dec 2023 23:33:27 GMT

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]

)

Re: Measure to give value of parent in parent/child relationship

DavidAtkins — Mon, 11 Dec 2023 08:23:18 GMT

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.

Re: Measure to give value of parent in parent/child relationship

Anonymous — Tue, 12 Dec 2023 02:17:12 GMT

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.

Re: Measure to give value of parent in parent/child relationship

Jack_D — Tue, 12 Dec 2023 05:46:05 GMT

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}))