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.
There is some theory on how the filter context interacts with a DAX Calculated Table variable, and how context transition happens, I am missing this.