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 need to put some values into buckets. I believe the switch function is the way to go, but I'm not sure how it works when I need one of the criteria to fall in between two values. This is what I need my formula to do:
TIME AT FINAL = SWITCH (
TRUE (),
'QA'[AVG TIME AT DESTINATION] <75,"0-75",
'QA'[AVG TIME AT DESTINATION] >75<105,"75-105"
'QA'[AVG TIME AT DESTINATION] >105,">105"
)
The middle part of the formula says that if AVG TIME AT DESTINATION is between 75-105, make the value "75-105". However, this does not work with the Switch function, at least not how I have it written. How would I accomplish this?
Thanks in advance for your help.
Solved! Go to Solution.
Two possible ways to do this:
TIME AT FINAL = SWITCH ( TRUE (), 'QA'[AVG TIME AT DESTINATION] <75,"0-75", 'QA'[AVG TIME AT DESTINATION] >75 && 'QA'[AVG TIME AT DESTINATION]<105,"75-105" 'QA'[AVG TIME AT DESTINATION] >105,">105" ) or TIME AT FINAL = SWITCH ( TRUE (), 'QA'[AVG TIME AT DESTINATION] <75,"0-75", ''QA'[AVG TIME AT DESTINATION] >105,">105", "75-105" )
Two possible ways to do this:
TIME AT FINAL = SWITCH ( TRUE (), 'QA'[AVG TIME AT DESTINATION] <75,"0-75", 'QA'[AVG TIME AT DESTINATION] >75 && 'QA'[AVG TIME AT DESTINATION]<105,"75-105" 'QA'[AVG TIME AT DESTINATION] >105,">105" ) or TIME AT FINAL = SWITCH ( TRUE (), 'QA'[AVG TIME AT DESTINATION] <75,"0-75", ''QA'[AVG TIME AT DESTINATION] >105,">105", "75-105" )
Nice, @Greg_Deckler
For future reference, is the a reason that there needs to be two && in the formula? Does that have a meaning?
&& is your logical AND filter construct shorthand. A single & is concatenation. You could have used the AND function as well. || is OR.
I'd suggest you might want to assign a variable here.
TIME AT FINAL =
Var avgtime = QA[AVG TIME AT DESTINATION]
RETURN
SWITCH (
TRUE ()
, avgtime <75,"0-75"
, avgtime >105,">105"
, "75-105"
)
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 |
---|---|
105 | |
96 | |
75 | |
63 | |
62 |
User | Count |
---|---|
135 | |
105 | |
104 | |
80 | |
65 |