Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Given the following table
ID | Parent ID | AMOUNT | PATH |
1 | 10 | 1 | |
2 | 1 | 20 | 1|2 |
3 | 1 | 30 | 1|3 |
4 | 2 | 40 | 1|2|4 |
5 | 2 | 50 | 1|2|5 |
I want to create a Parent-Child visualization using matrix in Power BI.
Currently using DAX method PATH and PATHITEM I have this:
ID Amount
1 150
2 110
4 40
5 50
3 30
Is it possible to have :
ID Amount
1 150
1 10
2 110
2 20
4 40
5 50
3 30
Solved! Go to Solution.
Hi, @Anonymous
Based on your description, I created data to reproduc your scenario. The pbix file is attached in the end.
Table:
You may create calculated columns as below.
Path = PATH('Table'[ID],'Table'[Parent ID])
Level1 = PATHITEM('Table'[Path],1,TEXT)
Level2 =
var _level2 = PATHITEM('Table'[Path],2,TEXT)
return
IF(
PATHITEM('Table'[Path],2,TEXT)="",
[Level1],
_level2
)
Level3 =
var _level3 = PATHITEM('Table'[Path],3,TEXT)
return
IF(
_level3="",
IF(
[Level2]="",
[Level1],
[Level2]
),
_level3
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
Based on your description, I created data to reproduc your scenario. The pbix file is attached in the end.
Table:
You may create calculated columns as below.
Path = PATH('Table'[ID],'Table'[Parent ID])
Level1 = PATHITEM('Table'[Path],1,TEXT)
Level2 =
var _level2 = PATHITEM('Table'[Path],2,TEXT)
return
IF(
PATHITEM('Table'[Path],2,TEXT)="",
[Level1],
_level2
)
Level3 =
var _level3 = PATHITEM('Table'[Path],3,TEXT)
return
IF(
_level3="",
IF(
[Level2]="",
[Level1],
[Level2]
),
_level3
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you @v-alq-msft that's what I wanted to do.
Just one last thing is it possible to avoid to repeat the node "1" three times?
(-)1
1
instead of
(-)1
(-)1
1
@Anonymous , refer if this can help
Thank you @amitchandak for your reply but in my case I don't have mutliple parents, when I unfold a parent node I just want to display the parent node among the children nodes, because the parent node has his own "amount" to display.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |