Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Get Fabric Certified for FREE during Fabric Data Days. Don't miss your chance! Request now
Hi,
I would like some help creating a measure that avoids summing duplicates and incorporates some conditional logic.
I'm very new to DAX and so it may be that what I am attempting is impossible or better achieved in an alternate way. Advice, greatly appreciated!
Given something similar to the following table:
I'd like to create a measure that avoids duplicates across all columns for CAT = A but only avoids duplicates across CAT and VALUE for CAT = B or C.
Essentially the measure should sum the following:
The following code successfully avoids duplicates across all columns but I am unsure how to ammend it to achieve the conditional logic described.
[measure] =
SUMX(
SUMMARIZE(
T,
T[DATE],
T[CAT]
T[VALUE]
),
T[VALUE]
)
The above essentially achieves:
However for CAT B and CAT C I guess the following should be applied:
[measure] =
SUMX(
SUMMARIZE(
T,
T[CAT],
T[VALUE]
),
T[VALUE]
)Many thanks in advance!
Solved! Go to Solution.
@mojo_flex , one of the ways to achieve this is to use the virtual tables you've created:
conditional_sum =
VAR t_A = SUMMARIZE ( T, T[DATE], T[CAT], T[Value] )
VAR t_BC = SUMMARIZE ( T, T[CAT], T[VALUE] )
VAR current_cat = SELECTEDVALUE ( T[Cat] )
RETURN
IF ( current_cat = "A", SUMX ( t_A, [Value] ), SUMX ( t_BC, [Value] ) )
Is this what you want to achieve?
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
@mojo_flex , can you, please, show the result you want to achieve?
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
@mojo_flex , one of the ways to achieve this is to use the virtual tables you've created:
conditional_sum =
VAR t_A = SUMMARIZE ( T, T[DATE], T[CAT], T[Value] )
VAR t_BC = SUMMARIZE ( T, T[CAT], T[VALUE] )
VAR current_cat = SELECTEDVALUE ( T[Cat] )
RETURN
IF ( current_cat = "A", SUMX ( t_A, [Value] ), SUMX ( t_BC, [Value] ) )
Is this what you want to achieve?
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
Answers my question perfectly. Thank you for taking the time to help me.
I was wondering if splitting my table was going to be neccessary as I wasn't aware of the virtual table function. I really appreciate the pointers!
Check out the November 2025 Power BI update to learn about new features.
Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!
| User | Count |
|---|---|
| 11 | |
| 9 | |
| 9 | |
| 6 | |
| 5 |
| User | Count |
|---|---|
| 27 | |
| 22 | |
| 19 | |
| 17 | |
| 11 |