I have a table containing weight which is measured almost every minute of an hour. I have data for about 10 days. The screenshot is below:
Value - Weight in KG
I separated date to date-only and time-only columns to relate the time-only column to the time dimension table.
The time dimension table is shown below which records every second, minute, and hour of a day and groups time by the hour, am/pm, bins, and so on.
I created a relationship as the picture below:
However, when I try to slice the average by hour number, I get the result shown below:
Blank rows and missing data. I know Friday has data from 2 am to 8 am but the table is not showing it at all.
the first row which is blank is supposed to be hour number 0 or 12 AM.
Please if anyone can tell me what is going on. I have wasted hours trying to figure out the solution.
you are trying to filter the data from non-filtering table. That could be the problem, try to change the cross filtering direction and check.
This does not solve the issue. I have the exact problem with my time dimension table as well. There should be no rows blank, as this is my FACT table with IDs. However, 6,690 rows are blank for just the month of March and I do not understand why.
Here is the schema. I am using the Time Dimension table from Radicad.
I am relating the "Time" in the dimension table to the "CreateTime" in the Fact. They are both Data type "Time". Changing these columns to type Text does not seem to work for me either. I can drop the PBIX file if needed.
Did anyone find the solution to this issue?
Take a look at the September 2023 Power BI update to learn more.
Join Microsoft Reactor and learn from developers.