Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi, I'd appreciaet your advice
I have 3 SharePoint lists (parent-chil) I pulled them into PBI like a table and created relationship in data model
the $$ amounts are related to parent not the child, so basically users enter their total domain cost and then provide some domain breakdown, but when I create a matrix and a measure of sum, the measure gets duplicated for every single sub domain.
how can I prevent that?
Solved! Go to Solution.
Hi, @Anonymous ;
You could create a measure as follow:
amount =
var _res=IF (
MAX ( 'Sub-Domain'[Sub-Domain] )
= CALCULATE (
MIN ( 'Sub-Domain'[Sub-Domain] ),
FILTER (
ALL ( 'Sub-Domain' ),
[Parent:Main Table ID] = MAX ( 'Main_Table'[Main Table ID] )
)
),
CALCULATE (
SUM ( 'Sub-Domain'[Amount $] ),
FILTER (
ALL ( 'Sub-Domain' ),
[Parent:Main Table ID] = MAX ( 'Main_Table'[Main Table ID] )
)
)
)
return IF(ISINSCOPE('Main_Table'[Main Table ID]),_res,SUM('Sub-Domain'[Amount $]
))
The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous ;
You could create a measure as follow:
amount =
var _res=IF (
MAX ( 'Sub-Domain'[Sub-Domain] )
= CALCULATE (
MIN ( 'Sub-Domain'[Sub-Domain] ),
FILTER (
ALL ( 'Sub-Domain' ),
[Parent:Main Table ID] = MAX ( 'Main_Table'[Main Table ID] )
)
),
CALCULATE (
SUM ( 'Sub-Domain'[Amount $] ),
FILTER (
ALL ( 'Sub-Domain' ),
[Parent:Main Table ID] = MAX ( 'Main_Table'[Main Table ID] )
)
)
)
return IF(ISINSCOPE('Main_Table'[Main Table ID]),_res,SUM('Sub-Domain'[Amount $]
))
The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Don't rely on the implicit measures. Write a custom measure that handles the computations according to your requirements.
User | Count |
---|---|
50 | |
40 | |
18 | |
14 | |
13 |
User | Count |
---|---|
100 | |
63 | |
28 | |
18 | |
13 |