The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
User | Count |
---|---|
10 | |
9 | |
6 | |
6 | |
5 |
User | Count |
---|---|
21 | |
14 | |
14 | |
9 | |
7 |