Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
schwinnen
Helper V
Helper V

Switch function between two values

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.

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

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"
)

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

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"
)

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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"

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.