Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello
I have custom column that has this code.
Solved! Go to Solution.
It works fine for me. It returns 1-2 minutes. I'd need to see your data to understand what is going on.
Rather than nested IF statements, consider switching to the SWITCH function. For example:
New Talk Time =
SWITCH(
TRUE(),
Maintel_Data[Talk_Time_Seconds] >= 0 && Maintel_Data[Talk_Time_Seconds] <= 30, "Under 30 Seconds",
Maintel_Data[Talk_Time_Seconds] > 30 && Maintel_Data[Talk_Time_Seconds] <= 60, "30-60 Seconds",
Maintel_Data[Talk_Time_Seconds] > 60 && Maintel_Data[Talk_Time_Seconds] <= 120, "1-2 Minutes",
Maintel_Data[Talk_Time_Seconds] > 120 && Maintel_Data[Talk_Time_Seconds] <= 180, "2-3 Minutes",
Maintel_Data[Talk_Time_Seconds] > 180 && Maintel_Data[Talk_Time_Seconds] <= 240, "3-4 Minutes"
)
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingIt works fine for me. It returns 1-2 minutes. I'd need to see your data to understand what is going on.
Rather than nested IF statements, consider switching to the SWITCH function. For example:
New Talk Time =
SWITCH(
TRUE(),
Maintel_Data[Talk_Time_Seconds] >= 0 && Maintel_Data[Talk_Time_Seconds] <= 30, "Under 30 Seconds",
Maintel_Data[Talk_Time_Seconds] > 30 && Maintel_Data[Talk_Time_Seconds] <= 60, "30-60 Seconds",
Maintel_Data[Talk_Time_Seconds] > 60 && Maintel_Data[Talk_Time_Seconds] <= 120, "1-2 Minutes",
Maintel_Data[Talk_Time_Seconds] > 120 && Maintel_Data[Talk_Time_Seconds] <= 180, "2-3 Minutes",
Maintel_Data[Talk_Time_Seconds] > 180 && Maintel_Data[Talk_Time_Seconds] <= 240, "3-4 Minutes"
)
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHello
I didn't think it was the data but after checking it was.
I was converting seconds but didn't relise it was only getting the seconds, I needed to add the hours and the minutes to my seconds field.
I used your switch code and it also worked.
Regards
GrahamR99
Excellent. Glad you got it sorted out, and learned a new function! 😁 SWITCH is so much easier to read and edit.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingBy the way @GrahamR99 I noticed a small bug in your code:
It will not handle 300 seconds. It will drop down to the "Over 6 hours" section. Make sure when you are coverting to SWITCH you fix that.
EDIT: actually, it is all of your code the rest of the way down. Seconds of exactly 420, 480, 540, etc. will fail.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI agree with @edhans,
Your code works fine for me, but a switch to Switch would be much cleaner and easier to read. Below are 2 columns, one with your code and one with edhans.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |