Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
DavidAtkins
Frequent Visitor

Measure to give value of parent in parent/child relationship

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 NumberLocationCount LocationParent
1A10 AA
1B15 BA
1C7 CA
2D68 DB
2E20 EB
3F34 FC
3G84 GC

 

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

LocationParentCount (Measure)Parent Count (Measure)
AA1010
BA1510
CA710
DB6815
EB2015
FC347
GC847

 

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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

 

vbinbinyumsft_0-1702347400881.png

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.

View solution in original post

4 REPLIES 4
Jack_D
Frequent Visitor

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

1.PNG

 

Anonymous
Not applicable

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

 

vbinbinyumsft_0-1702347400881.png

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
Frequent Visitor

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

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors