Count of instances by day and hour

[sam0007]

Spoiler

Hello PBI community,

I'm looking to count number of loads by hour. The dataset spans over two days and the table visual is calculating the measure incorrectly. What am I doing wrong here? How can I show the visual over two days?

I have some loads that are loaded on 22/06/2021 to be delivered on 23/06/2021. The matrix visual only displays 23/06/2021 data.

When DAX is calculating the measure it is counting by hour only and not on the date. Example:
for 18:00 hours, on 22/06/2021 @ 18:00 hours I have 6 loads and on 23/06/2021 @ 18:00 hours I have 1. However in the visual it is showing as 23/6/2021 @ 18:00 hours as 7 (which is 6 from 22/06 + 1 from 23/06)

I have selected cross filter direction as 'Both' for creating relationship between date and hour table with data table. 

Below is the link for power bi file and excel file which shows the difference.

Appreciate your help!

https://www.dropbox.com/sh/l94j7rb9iavm783/AADqJs6dOZU06anbmdfftyiUa?dl=0 

[sam0007]

@ERD That's amazing! Thanks for your help 🙂

[ERD · Accepted solution]

Hi @sam0007 ,

First, bi-directional relations are to be used in very rare cases. You don't need them here.

Second, here is the refernce to the correct usage of USERELATIONSHIP function: https://dax.guide/userelationship/ 

I don't know the final result you want to achieve, but from what you've mentioned, you can try to change your measure in the next way:

Count of loads by hour =
CALCULATE (
    COUNT ( f_DataTable[Route ID] ),
    USERELATIONSHIP ( f_DataTable[Load Date], d_DateTable[Date] ),
    USERELATIONSHIP ( f_DataTable[Load Time], d_HourTable[Time] )
)

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.