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
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 |
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.
Solved! Go to 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.
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
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.
Hi DavidAtkins
after creat relationship between table1,table2
use this calculated columns:
Count =
RELATED(
table1[count]
)
Parent Count =
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
34 | |
14 | |
14 | |
12 | |
9 |