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.
Can a Switch include both && and ||?
Looking for a rule that looks at a the [1. ### V2] (Jan through to Dec) field and if it is 3 or 5 for all 12 fields. If any of the 12 are not 3 or 5 then return 0, otherwise 1.
CY Activity = SWITCH( TRUE(), [1. Jan v2] = 3 || [1. Jan v2] = 5 && [1. Feb v2] = 3 || [1. Feb v2] = 5 , 1, 0)
I intend to extend this formula out to a Dec field, but the above isn't working per the below.
for reference - 3 is 'no spend' and grey in the image. 5 is the 'Future' and white in the image.
Solved! Go to Solution.
I think this might just be an order of operations issue I think that the logical AND is evaluated first, then the OR.
So effectively you have: condition1 || ( condition2 && condition3) || condition4
Whereas I think you want: (condition1 || condition2 ) && (condition3 || condition4)
I think adding extra brackets to force the order of operation you want might fix this:
CY Activity = SWITCH( TRUE(), ([1. Jan v2] = 3 || [1. Jan v2] = 5 ) && ([1. Feb v2] = 3 || [1. Feb v2] = 5 ) , 1, 0)
I often use extra bracket if I have multiple logical AND/OR combinations to clarify the ordering
Hi @StidifordN
Is [1. Jan v2] a column as below?
c | J | F | M | A |
1 | 1 | 1 | 1 | 1 |
2 | 1 | 1 | 1 | 1 |
3 | 2 | 1 | 2 | 1 |
4 | 2 | 2 | 2 | 2 |
5 | 3 | 2 | 3 | 3 |
6 | 4 | 4 | 1 | 4 |
7 | 4 | 4 | 4 | 4 |
8 | 3 | 3 | 1 | 3 |
9 | 4 | 4 | 4 | 1 |
10 | 3 | 1 | 3 | 1 |
11 | 4 | 4 | 2 | 4 |
12 | 5 | 5 | 5 | 1 |
Best Regards
Maggie
No, its a measure of nested if statements to result in numbers between 0 and 5 (excluding 2... can't remember why I did that).
I know I can do this a long way, was hoping for a more efficient version.
For each month I could do the below and it works. Was hoping the && might work.
CY Activity Jan = CALCULATE(SWITCH( TRUE(), [1. Jan v2] = 3 || [1. Jan v2] = 5 , 1, 0)) CY Activity Feb = CALCULATE(SWITCH( TRUE(), [1. Feb v2] = 3 || [1. Feb v2] = 5 , 1, 0))
And then do another that looks to all 12 for the resulting 1.
Hi @StidifordN
If you want "CY Activity" measure to meet all conditions,
for example,
condition 1: [Measure 1]=3||[Measure 1]=5
cy = SWITCH(TRUE(),([Measure 1]=3||[Measure 1]=5)&&([Measure 2]=3||[Measure 2]=5),1,0)
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I think this might just be an order of operations issue I think that the logical AND is evaluated first, then the OR.
So effectively you have: condition1 || ( condition2 && condition3) || condition4
Whereas I think you want: (condition1 || condition2 ) && (condition3 || condition4)
I think adding extra brackets to force the order of operation you want might fix this:
CY Activity = SWITCH( TRUE(), ([1. Jan v2] = 3 || [1. Jan v2] = 5 ) && ([1. Feb v2] = 3 || [1. Feb v2] = 5 ) , 1, 0)
I often use extra bracket if I have multiple logical AND/OR combinations to clarify the ordering
Champion!
@d_gosbell wrote:I think this might just be an order of operations issue I think that the logical AND is evaluated first, then the OR.
So effectively you have: condition1 || ( condition2 && condition3) || condition4
Whereas I think you want: (condition1 || condition2 ) && (condition3 || condition4)
I think adding extra brackets to force the order of operation you want might fix this:
CY Activity = SWITCH( TRUE(), ([1. Jan v2] = 3 || [1. Jan v2] = 5 ) && ([1. Feb v2] = 3 || [1. Feb v2] = 5 ) , 1, 0)I often use extra bracket if I have multiple logical AND/OR combinations to clarify the ordering
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 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |