CLOCK Table Filtering

[JavierLopezFALP]

Greetings.

I've got a growing appointments table (950k+, since 2023, well over 2 milllion if I up to 2021), for which I need numerous flags that depend on the appointment time. In order to reduce the size of the file, instead of making various calculated or grouped columns, I made a simple clock table, with the 86.400 values between 00:00:00 and 23:59:59, with columns for time-frames (every 15 min, every 30 min ,etc.). I used the following function to create the first column

List.Times(#time(0, 0, 0), 86400, #duration(0, 0, 0, 1))

Afterwards, I used M functions to add the timeframes and High-Low flags.

The relationship was built correctly, and it's active

However, by grouping a distinct count of all time values in the appointment table with the values of the clock table, plenty of values are left out, just as the following table shows (the language is spanish)

¿Why are there so many values that do not get related?

Thanks beforehand

[v-pnaroju-msft]

Hi JavierLopezFALP,

We kindly request you to mark your response as the accepted solution, as this will help other community members find answers to similar challenges more efficiently.
Please continue leveraging the Fabric Community for any further assistance with your queries.

Thank you.

[v-pnaroju-msft]

Hi JavierLopezFALP,

Thank you for sharing your insights and approach in resolving the issue.

We kindly request you to mark your response as the accepted solution, as this will help other community members find answers to similar challenges more efficiently.
Please continue leveraging the Fabric Community for any further assistance with your queries.

Thank you.

[v-pnaroju-msft]

Thankyou, @Deku, @kushanNa, for your response.

Hi JavierLopezFALP,

We appreciate your query posted on the Microsoft Fabric Community Forum.

Kindly find attached the screenshot and PBIX file, which may assist in resolving the issue:

If our response has been helpful, we would be grateful if you could mark it as the accepted solution and provide kudos. This will benefit other community members who may have similar queries.

Should you have any further questions, please do not hesitate to contact the Microsoft Fabric Community.

Thank you.

[JavierLopezFALP · Accepted solution]

I figured it out. Turns out that the date_time had miliseconds precision. But because  extracted the data as datetime, the format within PBI gave no clue about it. And the decimals remained even after "splitting" the time value.

Whereas my Clock_Table had "integer values behind" each time value.

I solved i with

cast (date_time as datetime2(0))

Thanks for your input.

[kushanNa]

Maybe this is happening becuase you have blank / incorrect format / error values in APP table 

for an example following is the sameple APP table with 3 values , 1 correct and two incorrect 

[JavierLopezFALP]

I'm sorry, I don't follow you. I did not want to bin the time values in the appointments table, with its growing 950k+ rows.

Which is why I made the 86.400 time values table. And it works, partially

The column on the left is form the CLOCK table, and the column on the right is from APP table. And some second-sensitive values are caught in the relationship.

Now, out of 913k appointments (after other filters, unrelated to this subject) few are left out of the relationship. But, those 913k share 48.807 distinct time values, and 48.369 are left inrelated.

So, what is the problem?

Thanks beforehand.

[Deku]

You also need the corresponding binned time in the appointment table to join on.

The associates ones look like they just happen to fall on the exact times in your clock table

For example a row in the appointment table with a time of 12:01 should either have a key of 12:00 or 12:15