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.
I'm tring to create a custom column which uses sum(Sales) but want to filter on another column call type where it shows everything EXCEPT Types that equal "JC" or "JD". Tried the following syntax but no joyy. Can anyone help? The sum part is working OK in a previous query but I've for type, i've tried "not in", "does not contain" and "<>" but cannot get the correct syntax.
if ([Sales] > 0 and ([TYPE] <> "JC" and [TYPE] <> "JD"))
then [Sales]
else [Net Amount]
Hi @magutter,
Please try it out in this file. Which measure is the one you want?
Sum1 = CALCULATE ( SUM ( Table1[Sales] ), FILTER ( ALL ( Table1 ), NOT Table1[Type] IN { "JC", "JD" } ) )
Sum2 = IF ( [Type] IN { "JC", "JD" }, [Net Amount], Table1[Sales] )
Sum3 = IF ( [Type] IN { "JC", "JD" }, CALCULATE ( SUM ( Table1[Net Amount] ) ), CALCULATE ( SUM ( Table1[Sales] ), FILTER ( ALL ( Table1 ), NOT Table1[Type] IN { "JC", "JD" } ) ) )
Best Regards,
Dale
Hi Dale
Thanks alot for your response. I know how to do it as a measure but what would you suggest when creating a custom column in the query editor. It would appear that custom columns uses "m" query language which I'm struggling with
Which result do you want?
Best Regards,
Dale
Hi Dale
I want it to get the NET figure for Sales but exclude TYPE of "JC" or "JD". I want those types to return a value of 0
Hi @magutter,
You can try it like this:
if [Sales] > 0 and [Type] <> "JC" and [Type] <> "JD"
then [Net Amount]
else 0
Best Regards,
Dale
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |