Power BI’s Pseudo-Time & Pseudo-Date Types

Once upon a time, I was bit by a time column which straightforward equality-comparison DAX wouldn’t filter as expected. From this saga, I learned a couple important lessons about Power BI’s data types that are not intuitively obvious from the user interface.

Imagine some simple DAX like the below. When evaluated, the result I’d receive back was blank—even though rows with the matching time could be plainly seen in Power BI’s table view.

CALCULATE (
  COUNTROWS ( Data ),
  Data[Timestamp] = TIME (12, 30, 0)
)

Why this strange behavior? Turns out, Power BI’s UI was not displaying the full data values in the column. When viewed in the UI’s table view, it looked like matching values existed in the column, but actually they didn’t. Comparisons were being performed correctly, just not against the time-only values I thought were there.

How did this unusual situation come about? The twist was that I’d used Power BI’s column data type dropdown to change the column’s type from date/time to time. (The data source was handling date/time values to Power BI, but the date component wasn’t of interest to me. To get rid of it, I updated the data type setting for the relevant column from date/time to date.)

After making that adjustment, in UI’s table view, the column appeared to be in exactly the desired state—it looked like a column of time values. However, DAX expressions comparing to these apparent time values didn’t work as expected. Also, strange behavior could be observed in the filter pane, where the same time value could be repeated multiple times.

Why?

At the root, the unexpected behaviors I observed stemmed from two factors:

• Power BI does not have true time or date data types. Instead, type date/time is used for both.
• Also—despite what its tooltip may indicate—changing Power BI’s column type dropdown menu between date/time, date and time does not change (i.e. transform) the underlying values that are stored in the column.

Behind the scenes, Power BI has a single date/time data type which it uses for date/time as well as date and time values. This data type stores each of its values as a decimal number. The whole number portion represents the number of days the given date/time value comes after Power BI’s epoch date (the date it considers day 0, which is 1899-12-30), while the fractional component represents the time portion of the value (e.g. .5 = halfway through the given day = 12 noon).

So, regardless of whether the column appears to contain dates, times, or date/times, its underlying data type is date/time. In the UI, setting the column’s type to what the UI calls type date or type time does not change this. Choosing one of these UI pseudo-types simply adds an annotation to the date/time column signaling the UI that the column is to appear like it contains date-only values or time-only values, respectively.

Power BI’s column data type dropdown can make it sound like a transformation will be applied to the underlying data (see the below tooltip, from the version of this dropdown that appears in the ribbon under Column tools), but for a date/time column being change to pseudo-type time or pseudo-type date, this is not the case. Changing a column’s type to date does not modify the column’s values to remove their time components, nor does setting the column to type time cause its values to have their date components zeroed-out. Again, choosing one of these UI pseudo-types simply adds an annotation on the relevant column instructing the UI to hide the respective time or date portion of the full date/time value, which is still stored in the column.

Now, can you figure out why DAX seemed to act funny for me? When I set the column’s type to time, I assumed that the values it contained would become time values, but they didn’t. They were still the original, full date/time values, just with the date portion hidden in the UI. Comparisons using DAX’s TIME function didn’t work as I expected because, behind-the-scenes, effectively that function is outputting date/time values with the day part set to 0 (i.e. the epoch date). As the values in my column all had non-epoch day components, trying to equality filter them to something dated back to day 0 didn’t produce any matches.

The simple solution: Perform the date/time to time conversion upstream of Power BI. If I had converted the date/time column to time in Power Query, the column’s values would have been imported into Power BI as pseudo-time values with their date component set to day 0, and DAX’s TIME function could have been used the way I expected.

Try It Out

If you want to play with this, use the following Power Query to populate a table:

#table(
    type table [Timestamp = datetime],
    {
        { #datetime(2023, 5, 15, 12, 30, 0) },
        { #datetime(2024, 8, 28, 12, 30, 0) }
    }
)

Then, in Power BI, create a calculated column that simply mirrors the source column.

TimestampClone = Data[Timestamp]

Now, in Power BI, change the type of the source column (i.e. Timestamp) to time or, if you prefer, to date.

Notice that the calculated column still shows the full underlying date/time values. This tells us that both the date and time components are still present in the source column, even though the table view UI does not show the full date/time values.

Bonus

Can you figure out why the filter pane displayed duplicate time values in the problem scenario? Hint: Using the table just created, run a trace in DAX Studio to see the DAX used to populate the filter pane.