I have a Microsoft Power BI (.pbix) file. I need to develop a Calculated Table using DAX language.
(I do not want a Power Query based solution.)
Scenario:
I have a simple table called 'SourceInput', it has data for an EmployeeId, DeviceId, and OperationDate.
The above three are the proper GROUP BY columns, in SQL terms.
The table has one more column called OperationStartTime.
Now, for each combination of the first 3 columns (EmployeeId, DeviceId, and OperationDate), we need to derive the MINIMUM OperationStartTime, with a condition:
Get the MINIMUM OperationStartTime for a given combination of EmployeeId, DeviceId, and OperationDate, only if the OperationStartTime falls after 12:00 hrs, i.e get the MINIMUM OperationStartTime AT or AFTER 12 PM for a given combination of EmployeeId, DeviceId, and OperationDate.
If there is no OperationStartTime at or after 12 PM for a given combination of EmployeeId, DeviceId, and OperationDate, then we show BLANK().
Note that while the OperationDate column is a date, the OperationStartTime column has a datetime value.
Please see the image file below:
I have developed a simple DAX based Calculated Table as below, this works fine, you can see the .pbix file.
DesiredOuput_DAX_CalculatedTable
=
ADDCOLUMNS (
SUMMARIZE(
SourceInput,
SourceInput[EmployeeId],
SourceInput[DeviceId],
SourceInput[OperationDate],
SourceInput[OperationStartTime]
),
"EarliestOperationStartTimeAfter12PM", CALCULATE(
MIN(SourceInput[OperationStartTime]),
FILTER(
VALUES(SourceInput[OperationStartTime]),
HOUR(SourceInput[OperationStartTime]) >= 12
--i.e. after 12 PM OperationStartTime only--
)
)
)
However, in my real project, the 'SourceInput' table has too many columns, and we need to remove the undesired ones, so I had to use DAX variables for the intermediate steps.
I developed an equivalent DAX based Calculated Table as below, using another source table called 'SourceInputExpanded' with variables to store data for the intermediate steps:
DesiredOuput_DAX_CalculatedTable_WithVariables
=
VAR SourceInput_CT_Variable = SELECTCOLUMNS(
SourceInputExpanded,
"EmployeeId1", SourceInputExpanded[EmployeeId],
"DeviceId1", SourceInputExpanded[DeviceId],
"OperationDate1", SourceInputExpanded[OperationDate],
"OperationStartTime1", SourceInputExpanded[OperationStartTime]
--remove undesired columns using this calculated table variable--
)
VAR Output_CT_Variable = ADDCOLUMNS (
SUMMARIZE(
SourceInput_CT_Variable,
[EmployeeId1],
[DeviceId1],
[OperationDate1],
[OperationStartTime1]
),
"EarliestOperationStartTimeAfter12PM", CALCULATE(
MINX(
SourceInput_CT_Variable,
[OperationStartTime1]
),
FILTER(
SUMMARIZE(
SourceInput_CT_Variable,
[OperationStartTime1]
),
HOUR([OperationStartTime1]) >= 12
--i.e. after 12 PM OperationStartTime only--
)
)
)
RETURN Output_CT_Variable
I am not getting the desired output.
Where am I in error in my second approach with variables ?
Can someone correct this ?
I have not fully adapted to using a DAX calculated table variable, and its' columns, there are some tricky logics in the syntax related to its' lineage....
The EarliestOperationStartTimeAfter12PM column of the output DAX Calculated Table is incorrect, the GROUP BY part is not working in the second case.
Not sure I fully understand your premise but here is how I would approach it.
Hi @snph1777q
I modified your formula a bit and added a calculated column.
DesiredOuput_DAX_CalculatedTable_WithVariables1 =
VAR SourceInput_CT_Variable =
SELECTCOLUMNS (
SourceInputExpanded,
"EmployeeId1", SourceInputExpanded[EmployeeId],
"DeviceId1", SourceInputExpanded[DeviceId],
"OperationDate1", SourceInputExpanded[OperationDate],
"OperationStartTime1", SourceInputExpanded[OperationStartTime] --remove undesired columns--
)
RETURN
SourceInput_CT_Variable
EarliestOperationStartTimeAfter12PM =
CALCULATE (
MIN ( DesiredOuput_DAX_CalculatedTable_WithVariables1[OperationStartTime1] ),
FILTER (
ALLEXCEPT (
DesiredOuput_DAX_CalculatedTable_WithVariables1,
DesiredOuput_DAX_CalculatedTable_WithVariables1[DeviceId1],
DesiredOuput_DAX_CalculatedTable_WithVariables1[OperationDate1]
),
TIMEVALUE ( DesiredOuput_DAX_CalculatedTable_WithVariables1[OperationStartTime1] )
> TIMEVALUE ( "12:00 PM" )
)
)
Is this the result you expect?
For more details about ALLEXCEPT and FILTER function, you can read related document link:
ALLEXCEPT function (DAX) - DAX | Microsoft Learn
FILTER function (DAX) - DAX | Microsoft Learn
Best Regards,
Community Support Team _Yuliax
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @snph1777q
I modified your formula a bit and added a calculated column.
DesiredOuput_DAX_CalculatedTable_WithVariables1 =
VAR SourceInput_CT_Variable =
SELECTCOLUMNS (
SourceInputExpanded,
"EmployeeId1", SourceInputExpanded[EmployeeId],
"DeviceId1", SourceInputExpanded[DeviceId],
"OperationDate1", SourceInputExpanded[OperationDate],
"OperationStartTime1", SourceInputExpanded[OperationStartTime] --remove undesired columns--
)
RETURN
SourceInput_CT_Variable
EarliestOperationStartTimeAfter12PM =
CALCULATE (
MIN ( DesiredOuput_DAX_CalculatedTable_WithVariables1[OperationStartTime1] ),
FILTER (
ALLEXCEPT (
DesiredOuput_DAX_CalculatedTable_WithVariables1,
DesiredOuput_DAX_CalculatedTable_WithVariables1[DeviceId1],
DesiredOuput_DAX_CalculatedTable_WithVariables1[OperationDate1]
),
TIMEVALUE ( DesiredOuput_DAX_CalculatedTable_WithVariables1[OperationStartTime1] )
> TIMEVALUE ( "12:00 PM" )
)
)
Is this the result you expect?
For more details about ALLEXCEPT and FILTER function, you can read related document link:
ALLEXCEPT function (DAX) - DAX | Microsoft Learn
FILTER function (DAX) - DAX | Microsoft Learn
Best Regards,
Community Support Team _Yuliax
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helpful resources
Join our Fabric User Panel
Share feedback directly with Fabric product managers, participate in targeted research studies and influence the Fabric roadmap.
Power BI Monthly Update - February 2026
Check out the February 2026 Power BI update to learn about new features.
