Sum of hours, based on values from second table

[Anonymous]

I am trying to calculate a total sum of hours, using two tables and a relationship. My current config is like this:

Table 'Data':

'data'[date]

'data'[unrelated_info_a]

'data'[unrelated_info_b]

'data'[weekday]  // 1 (Mon) - 7 (Sun), is calculated based on [date] and has a relationship with 'opening_hours'[weekday].

Table 'Opening Hours':

'opening_hours'[day] // Mon - Sun

'opening_hours'[weekday] // 1 (Mon) - 7 (Sun) and has a relationship with 'data'[weekday].

'opening_hours'[unrelated_info_a]

'opening_hours'[unrelated_info_b]

'opening_hours'[duration] // HH:mm:ss

In my Data table I have a list of values. Related to my issue, this is an example:

date	unrelated_info_a	week day
12-10-2020	<random data>	1
12-10-2020	<random data>	1
13-10-2020	<random data>	2
15-10-2020	<random data>	4
18-10-2020	<random data>	7
19-10-2020	<random data>	1

I'm having a hard time to get the total amount of hours per unique date from the opening_hours table.

Let's say all the 'opening_hours'[duration] values are 08:00:00 for every week day. The outcome I am trying to get is '40', since there are 5 unique dates. If one of those days was set in opening_hours with a duration of 6 hours (Sun the 18th, for example), the outcome should be '38'.

The reason I can't seem to solve this is because I thought I needed the product (SUMX) with the unique dates somehow, but it seems not possible to use this with multiple tables. Thus: SUMX isn't the way to go, or so it seems. Can someone give me a hand on using the correct type of formula?

[DataInsights · Accepted solution]

@Anonymous,

Try this measure:

Total Hours = 
VAR vDates =
    ALL ( Data[date], Data[weekday] )
VAR vResult =
    SUMX (
        vDates,
        VAR vDate = Data[date]
        VAR vWeekday = Data[weekday]
        VAR vOpeningHours =
            FILTER ( ALL ( OpeningHours ), OpeningHours[weekday] = vWeekday )
        VAR vDuration =
            MAXX ( vOpeningHours, OpeningHours[duration] )
        RETURN
            vDuration
    )
RETURN
    vResult

[Anonymous]

It needed some adjustments with apostrophes for the tables, but it worked right away. Very helpful to search for some documentation to understand better on what it is doing exactly, so thanks a lot!