Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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):
User | Count |
---|---|
58 | |
46 | |
19 | |
18 | |
15 |
User | Count |
---|---|
116 | |
41 | |
40 | |
28 | |
22 |