Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi,
Seeing as this is a DAX issue, I thought I would try my luck here with the DAX experts.
I currently have an 'execution_time' column in the format below:
What I would like is to create a new column which simply states "Day" if the time is between 7am-8:59pm, and "Night" if it's between 9pm-6:59am.
I found this formula which I thought could work for me, but when I tested it, it only returns "Night" under all conditions:
And when I amended the formula to suit my needs, the new column doesn't bring up anything (blank):
Also, just to provide some background, I'm running data via Direct Query, so it doesn't allow me to change the 'execution_time' format by creating a new column in the desktop. It looks like the FORMAT function is not supported by Direct Query?
I also tried changing the format by going to the Model view, clicking on the 'execution_time' column name and then under Properties, changing the format to Time only, but as you can see, this had no impact on the result:
Happy to provide more information if needed.
Thanks.
Solved! Go to Solution.
@hankmobley
Here is the complete solution
When using direct query, creating a calculated column based on time calculations is not allowed. Therefore, we can follow these steps to work around it
1. Create Day/Night measure based on time calculations
DayNightt Measure =
MAXX (
autofxx_order_leg,
VARCurrentTimee =
TIME ( HOUR (autofxx_order_leg[execution_time] ), MINUTE (autofxx_order_leg[execution_time] ), SECOND (autofxx_order_leg[execution_time] ) )
RETURN
IF (
CurrentTimee > TIMEVALUE ( "07:00 AM" )
&&CurrentTimee <= TIMEVALUE ( "09:00 PM" ),
"Day",
"Night"
)
)
2. Create a disconnected slicer table:
Slicer Day/Night = SELECTCOLUMNS ( { "Day", "Night" }, "Day/Night", [Value] )
3. Recreate the existing measures following this template
New Measure =
VARSelectedDayNightt =
SELECTEDVALUE ( 'Slicer Day/Night'[Day/Night] )
RETURN
CALCULATE (
[Old Measure],
FILTER (autofxx_order_leg, [DayNight Measure] =SelectedDayNightt )
)
4. Create the slicer from the Day/Night slicer table and use the new measure in your visual
5. If columns are required in the visual they shall be added as measures, example:
Profit =
VARSelectedDayNightt =
SELECTEDVALUE ( 'Slicer Day/Night'[Day/Night] )
RETURN
IF (
HASONEVALUE ( 'Slicer Day/Night'[Day/Night] ),
CALCULATE (
SUM (autofxx_order_leg[portfolio_base_pnl] ),
FILTER (autofxx_order_leg, [DayNight Measure] =SelectedDayNightt )
),
SUM (autofxx_order_leg[portfolio_base_pnl] )
)
@hankmobley
Here is the complete solution
When using direct query, creating a calculated column based on time calculations is not allowed. Therefore, we can follow these steps to work around it
1. Create Day/Night measure based on time calculations
DayNightt Measure =
MAXX (
autofxx_order_leg,
VARCurrentTimee =
TIME ( HOUR (autofxx_order_leg[execution_time] ), MINUTE (autofxx_order_leg[execution_time] ), SECOND (autofxx_order_leg[execution_time] ) )
RETURN
IF (
CurrentTimee > TIMEVALUE ( "07:00 AM" )
&&CurrentTimee <= TIMEVALUE ( "09:00 PM" ),
"Day",
"Night"
)
)
2. Create a disconnected slicer table:
Slicer Day/Night = SELECTCOLUMNS ( { "Day", "Night" }, "Day/Night", [Value] )
3. Recreate the existing measures following this template
New Measure =
VARSelectedDayNightt =
SELECTEDVALUE ( 'Slicer Day/Night'[Day/Night] )
RETURN
CALCULATE (
[Old Measure],
FILTER (autofxx_order_leg, [DayNight Measure] =SelectedDayNightt )
)
4. Create the slicer from the Day/Night slicer table and use the new measure in your visual
5. If columns are required in the visual they shall be added as measures, example:
Profit =
VARSelectedDayNightt =
SELECTEDVALUE ( 'Slicer Day/Night'[Day/Night] )
RETURN
IF (
HASONEVALUE ( 'Slicer Day/Night'[Day/Night] ),
CALCULATE (
SUM (autofxx_order_leg[portfolio_base_pnl] ),
FILTER (autofxx_order_leg, [DayNight Measure] =SelectedDayNightt )
),
SUM (autofxx_order_leg[portfolio_base_pnl] )
)
Thank you so much @tamerj1 , that works great!
I really appreciate the time and effort you have taken to helping me 😊
@hankmobley
Try this for a calculated column
DayNight =
VAR CurrentTime =
TIME ( HOUR ( autofx_order_leg[execution_time] ), MINUTE ( autofx_order_leg[execution_time] ), SECOND ( autofx_order_leg[execution_time] ) )
RETURN
IF (
CurrentTime > TIMEVALUE ( "07:00 AM" )
&& CurrentTime <= TIMEVALUE ( "09:00 PM" ),
"Day",
"Night"
)
Hi @tamerj1 ,
Thanks, but I"m still getting the same error message when I add your formula in a new column, and then use it in a visual (screenshot below):
Could it be the way the 'execution_time' column is formatted from DQ (screenshot below):
@hankmobley
Ok then try adding it as a measure. Use the following code
DayNight Measure =
MAXX (
autofx_order_leg,
VAR CurrentTime =
TIME ( HOUR ( autofx_order_leg[execution_time] ), MINUTE ( autofx_order_leg[execution_time] ), SECOND ( autofx_order_leg[execution_time] ) )
RETURN
IF (
CurrentTime > TIMEVALUE ( "07:00 AM" )
&& CurrentTime <= TIMEVALUE ( "09:00 PM" ),
"Day",
"Night"
)
)
Thanks @tamerj1
The measure does work on its own, but now I am not able to us the day/night data as part of my visuals that use slicers/filters. If I'm not mistaken, only columns work with slicers/filters?
I also checked the DQ options, and I don't have the same option you are showing. This is the only option I see under DQ:
Is there another workaround using DAX?
Apparently dq does not allow creating collumns baes on time. This resonable. Best t I think we can work around that.
Am not on my compter right now. Will get back to you on Monday morning. Apologies for that
Hi @hankmobley
You can store the time of the current row in a variable then use it in IF or SWITCH
AVR CurrentTime =
TIME (
HOUR ( autofx_order_leg[execution_time] ),
MINUTE ( autofx_order_leg[execution_time] ),
SECOND ( autofx_order_leg[execution_time] )
)
Hi @tamerj1 ,
Thanks for your reply. I created a new column and added your formula, but I get the below error:
Also, I'm not sure what you mean by "...then use it in IF or SWITCH". Sorry, PBI is relatively new to me and I'm still learning.
Thanks,
H
No worries can you please share the formula you've used.
My understanding that you wanted to create a calculated column not a measure. But again no worries it can be converted into a measure. Just paste your code in a reply and I will edit it for you
Hi @tamerj1 ,
I did provide screenshots above of the code I'm using, but I probably should have provided them in code form instead, sorry. Also, I am trying to create a column with Day/Night, not a measure. t's my understanding (although I could be wrong) that you can only use slicers on columns and not measures?
Here is the code I'm using to calculate Day/Night:
DayNight =
IF (
autofx_order_leg[execution_time] > TIMEVALUE("07:00 AM")
&& autofx_order_leg[execution_time] <= TIMEVALUE("09:00 PM"),
"Day",
IF (
autofx_order_leg[execution_time] > TIMEVALUE("09:00 PM")
&& autofx_order_leg[execution_time] <= TIMEVALUE("07:00 AM"),
"Night"
)
)
As mentioned, this code doesn't bring anything up (blank). To check this, I just opened up a new table that shows the 'execution_time' column next to the new 'Day/Night' column (screenshot below):
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
14 | |
13 | |
11 | |
9 | |
8 |