The following (code in red) works when run in the Power Bi Desktop version, but as soon as you replace it by the variable name, which contains the same code, Power BI generates an error. In a nutshell, the column [UnreachableSince] is of data type TEXT containing textual representations of DATETIME values and other non-datetime values (I know and there is nothing I can do about this).
To intercept the DATETIME values, therefore an IF() is used. It all works fine until you decide to use the variable name in place of the inline code inside the nested IF().
So this OK:
DATEDIFF(DATEVALUE([UnreachableSince]) + TIMEVALUE([UnreachableSince]), [ClosedDT], SECOND)
But this NOT OK!
DATEDIFF(__unreachable_dt, [ClosedDT], SECOND),
Code here (sorry but can't add colors when using Insert/Edit code sample from this editor toolbar):
ADDCOLUMNS(
GROUPBY(
ADDCOLUMNS(
CALCULATETABLE(
SELECTCOLUMNS(
...
),
FILTER(
...
)
),
"DownTime", IF ( [ErrorCategory] == "Unreachable",
VAR __unreachable_dt = DATEVALUE([UnreachableSince]) + TIMEVALUE([UnreachableSince])
VAR __unreachable_dt_in_secs = DATEDIFF(__start_date, __unreachable_dt, SECOND)
RETURN
IF( __unreachable_dt_in_secs >= 0,
DATEDIFF(DATEVALUE([UnreachableSince]) + TIMEVALUE([UnreachableSince]), [ClosedDT], SECOND),
BLANK() //DATEDIFF([CreationDT], [ClosedDT], SECOND)
),
DATEDIFF([CreationDT], [ClosedDT], SECOND)
)
),
[AssetName],
"DownTime", SUMX(CURRENTGROUP(), [DownTime])
),
"% up", 1 - ([DownTime] / __in_service_time)
)
So am I imagining things, or isn't this a DAX interpreter bug?
