date time Relationship

[Sujit_Thakur]

Dear all, I

have two tables Table A and Table B 

Table A is a event where for every day we store records for every event along with time for eg 

date column.   Time column 

8/11/2020          2:30 pm

8/11/2020          5:32 pm

8/11/2020          7:46 pm 

And rest Data in other columns 

While in table B we save data according time events like 

Date.           From time.     To time 

8/11/2020.       2:00 pm       5:00 pm

8/11/2020.       5:00 pm.       8:00 pm

I am creating a master calender table with DAX

master calender = Calender (MIN(TableA(Date column)),MAX(TableA(Date column)))

And will give relation to dates of both table to master calender date table column.

Now my issue is how can I relate time stamp so that time from Table A realtes with Time to and time from of Table B 

So that when I ask my visuals to call upon time stamp of from 5:00 to 6:00 they call upon data from Table A data which lies between 5 and 6 pm

Please help 

Regards 

Sujit Thakur 

[v-shex-msft · Accepted solution]

Hi @Sujit_Thakur,

It is impossible to build relationships as same as your requirement.

In my opinion, I'd like to suggest you write a measure formula to check records based on your selections and use on 'visual level filter' to filter records.

You can create two slicers that no direct effect the visual to pick up selected values of date values and time range.

Measure =
VAR selectedDate =
    MAX ( Calendar[Date] )
VAR timeRange =
    ALLSELECTED ( Selecotr[Hour] )
VAR currDate =
    MAX ( Table[Date] )
VAR currTime =
    MAX ( Table[Time] )
RETURN
    IF (
        curDate = selectedDate
            && AND (
                HOUR ( currTime ) >= MINX ( timeRange, [Hour] ),
                HOUR ( currTime ) <= MAXX ( timeRange, [Hour] )
            ),
        "Y",
        "N"
    )

Reference link:

Applying a measure filter in Power BI 

Regards,

Xiaoxin Sheng

[amitchandak]

@Sujit_Thakur , Just like common date table have common time table too

https://kohera.be/blog/power-bi/how-to-create-a-time-table-in-power-bi-in-a-few-simple-steps/

[Sujit_Thakur]

Dear @amitchandak sir ,

Yes this example video was very relevant 

Thanks for that 

But my problem has To and from time in Table B where I have to relate Table A and Table B in such a way that To time and from time column gets related with Time stamp of Table A 

Is that even possible ??

Please help 

Regards 

Sujit