Function Related doesn't seem to work

[RonaldvdH]

Hey guys,

Im working on a formula that calculates the amount of hours between 2 dates based on working days.

I've been wachting a clip on how to do that and i'm using this formula

Doorlooptijd = 
VAR ServiceWindowStart = Calculate(SELECTEDVALUE(ServiceWindow[ServiceWindow]),ServiceWindow[Dag]="Start")
VAR ServiceWindowEind = Calculate(SELECTEDVALUE(ServiceWindow[ServiceWindow]),ServiceWindow[Dag]="Eind")
VAR ServiceWindowPerDag = VALUE(ServiceWindowEind-ServiceWindowStart)*24
VAR DezeStartTijd = Tickets[StartTijd]
VAR DezeEindTijd = Tickets[EindTijd]
VAR StartDatum =Tickets[StartDate]
VAR EindDatum = Tickets[EndDate]
VAR EersteDagVerstrekenTijd = Switch(TRUE(),
                               Related('Date'[ServiceWindow])=0,0,                      
                               DezeStartTijd>=ServiceWindowEind,0,                      
                               DezeStartTijd<=ServiceWindowStart,ServiceWindowPerDag,   
                               StartDatum = EindDatum && DezeEindTijd < ServiceWindowEind,Round((DezeEindTijd-DezeStartTijd)*24,3),   
                               ROUND((ServiceWindowEind-DezeStartTijd)*24,3))          
VAR LaatsteDagVerstrekenTijd = Switch(TRUE(),  
                               LOOKUPVALUE('Date'[ServiceWindow],'Date'[Date],EindDatum)=0,0,
                               DezeEindTijd<=ServiceWindowStart,0,                      
                               DezeEindTijd>=ServiceWindowEind,ServiceWindowPerDag,     
                               StartDatum = EindDatum ,0,                               
                               ROUND((DezeEindTijd-ServiceWindowStart)*24,3))           
VAR VolledigeWerkdagen = Calculate (sum('Date'[ServiceWindow]),DATESBETWEEN('Date'[Date],StartDatum+1,EindDatum-1)) 
VAR TotaalAantalUren = EersteDagVerstrekenTijd+VolledigeWerkdagen*ServiceWindowPerDag+LaatsteDagVerstrekenTijd
Return TotaalAantalUren

The part where it calculates the FullWorkinDays (=VolledigeWerkdagen) works as a charm but the part where it needs to calculate the amounts of hours on the StartDate and EndDate (=Var EersteDagVerstrekenTijd en Var LaatsteDagVerstrekenTijd) doesn't work because the result is always 0

When I brake down the formula the result from the first part of the formula Related('Date' [ServiceWindow])=0,0. always returns a 0.

In my Data table I have a column that defines the ServiceWindow as following Sundays and Hollidays return a 0 and the other days return a 1.

The only thing i can come up with is that this formula doesn't find/accept the Related table/column but my relationships between both tables are correct (see below)

Both StartDate and EndDate are linked to Date

[lbendlin]

Here is a version for the calculated column that does not require the extra table. Start is the concatenation of StartDate and StartDateTime, End is the concatenation of EndDate and EndDateTime

BusHours Column = 
VAR b =
    SELECTCOLUMNS (
        ADDCOLUMNS (
            FILTER (
                CROSSJOIN ( CALENDAR ( [StartDate], [EndDate] ), GENERATESERIES ( 0, 1439 ) ),
                WEEKDAY ( [Date], 2 ) < 7
                && [Value] >= 480
                && [Value] < IF(WEEKDAY ( [Date], 2 )=6,720,1200)
            ),
            "Min",
                [Date] * 1440 + [Value]
        ),
        "Value", [Min]
    )
RETURN
    COUNTROWS (
        INTERSECT (
            GENERATESERIES ( [Start] * 1440 ,  [End] * 1440  - 1 ),
            b
        )
    ) / 60

[lbendlin]

I will change your sample data to include a weekend, ok?

Also - there is no UNIONX function in DAX  (please vote for my idea)  so this is something that is much easier to do in Power Query.

[isjoycewang]

Hi,

There are multiple VAR in your formula but in fact they're not working in the final result.

The only one impacts your result is below:

VAR EersteDagVerstrekenTijd = Switch(TRUE(),
                                RELATED('Date'[ServiceWindow])=0,0)
                               
Return EersteDagVerstrekenTijd

In this DAX, it describes that if Related('Date'[ServiceWindow]) = 0, your result would be zero; otherwise it'll be BLANK() due to no definition.

What's your expected result? If you expect that if the Related('Date'[ServiceWindow]) = 1, then you could get 1 in the 'Tickets' table, please try below:

Doorlooptijd = 
//VAR ServiceWindowStart = Calculate(SELECTEDVALUE(ServiceWindow[ServiceWindow]),ServiceWindow[Dag]="Start")
//VAR ServiceWindowEind = Calculate(SELECTEDVALUE(ServiceWindow[ServiceWindow]),ServiceWindow[Dag]="Eind")
//VAR ServiceWindowPerDag = VALUE(ServiceWindowEind-ServiceWindowStart)*24
//VAR DezeStartDatum = [StartDate]
VAR EersteDagVerstrekenTijd = Switch(TRUE(),
                                RELATED('Date'[ServiceWindow])=0,0,1)

    Return EersteDagVerstrekenTijd

Best Regards,

Joyce

[RonaldvdH]

@isjoycewang the expected result is that it checks the value for ServiceWindow for that specific date in the (related) date table.

If it returns 0 then that date isn't in the servicewindow and if it returns a 1 then it IS in the servicewindow

[RonaldvdH]

@isjoycewang thanks for responding but i was workin on my formula a bit more. Could you recheck my post seeing i've altered the formula and my initial post

[isjoycewang]

RELATED() should be able to apply in the SWITCH() function.

Please create a table containing columns from 'Date' and 'Tickets' table to see if they have valid relationship. If not, please try to change column data type or check settings to make them connected.

Another way is to use LOOKUPVALUE() function to get the 'Date'[ServiceWindow] value as you used in the VAR LaatsteDagVerstrekenTijd. Does it work for you? Thanks.

[RonaldvdH]

@isjoycewang if I change the first part of the formula from Related to LookupValue the result is the same. So the formula doesn't work because it just can't find the correct value for ServiceWindow in the Date table and just returns 0 every time.

I already have a summarized table based on both Tables called Tickets. It summarizes the table based on Ticket ID's and then uses this formula to copy the StartDate and EndDate as wel as StartTijd and Eindtijd and that works

StartDate = 
VAR __Tickets = Tickets[WMS_ID]
VAR __Incidenten = FILTER(ALL('Incidenten'),'Incidenten'[WMS_ID] = __Tickets)
RETURN
MAXX(__incidenten,[StartDate])

[RonaldvdH]

@isjoycewang strange thing because my New table called Tickets can get values from the table Incidents but not from the table Date through this formula 

ServiceWindow = 
VAR __StartDate = Tickets[StartDate]
VAR __Date = FILTER(ALL('Date'),'Date'[date] = __StartDate)
RETURN
MAXX(__Date,[ServiceWindow])

and the relationships are correct or do you see something i'm missing ?

 

[RonaldvdH]

@isjoycewang i'm still working on this formula but I just can't seem to get the data out of the Date table into my Tickets Table.

But I can get stuf out of my IncidentsTable but that means I go through the Date Table so what does that mean ?

There has to be something wrong with the relationship but i'm not sure what. 

A part of the formula gets data from the date table (for example, it does return a value based on DatesBetween) so full days it can calculate.

Doorlooptijd = 
VAR ServiceWindowStart = Calculate(SELECTEDVALUE(ServiceWindow[ServiceWindow]),ServiceWindow[Dag]="Start")
VAR ServiceWindowEind = Calculate(SELECTEDVALUE(ServiceWindow[ServiceWindow]),ServiceWindow[Dag]="Eind")
VAR ServiceWindowPerDag = VALUE(ServiceWindowEind-ServiceWindowStart)*24
VAR DezeStartTijd = Tickets[StartTijd]
VAR DezeEindTijd = Tickets[EindTijd]
VAR StartDatum =Tickets[StartDate]
VAR EindDatum = Tickets[EndDate]
VAR EersteDagVerstrekenTijd = Switch(TRUE(),
                               Related('Date'[ServiceWindow])=0,0,                      
                               DezeStartTijd>=ServiceWindowEind,0,                      
                               DezeStartTijd<=ServiceWindowStart,ServiceWindowPerDag,   
                               StartDatum = EindDatum && DezeEindTijd < ServiceWindowEind,Round((DezeEindTijd-DezeStartTijd)*24,3),   
                               ROUND((ServiceWindowEind-DezeStartTijd)*24,3))          
VAR LaatsteDagVerstrekenTijd = Switch(TRUE(),  
                               LOOKUPVALUE('Date'[ServiceWindow],'Date'[Date],EindDatum)=0,0,
                               DezeEindTijd<=ServiceWindowStart,0,                      
                               DezeEindTijd>=ServiceWindowEind,ServiceWindowPerDag,     
                               StartDatum = EindDatum ,0,                               
                               ROUND((DezeEindTijd-ServiceWindowStart)*24,3))           
VAR VolledigeWerkdagen = Calculate (sum('Date'[ServiceWindow]),DATESBETWEEN('Date'[Date],StartDatum+1,EindDatum-1)) 
VAR TotaalAantalUren = EersteDagVerstrekenTijd+VolledigeWerkdagen*ServiceWindowPerDag+LaatsteDagVerstrekenTijd
Return TotaalAantalUren

 The formula part(s) that don't work are the VAR EersteDagVerstrekenTijd en VAR LaatsteDagVerstrekenTijd and I don't know why ?

Here you can see the results of the formula if I break them down, you can see that the VAR EersteDagVerstrekenTijd /LaatsteDagVerstrekenTijd  don't work and the last part does

@amitchandak or @lbendlin maybe you know the solution ?

[lbendlin]

RELATED should not be used in measures, only in calculated columns.

[RonaldvdH]

@lbendlin thanks for your reply

In this instance the question was based on a calculated column (not a measure) but nevertheless it doesn't work

[lbendlin]

Any chance of getting meaningful sample data and an indication of the expected results?

[RonaldvdH]

@lbendlin the sample data was in the picture basically it's a couple of columns with a startdate/time and an enddate/time and then calculate the number of business hours between the two dates

For example:

WMS_ID	StartDate	StartDateTime	EndDate	EndDateTime	BusinessHours
WMS000512325	maandag 14 november 2022	14:22:00	donderdag 17 november 2022	16:20:00
WMS000512326	maandag 14 november 2022	15:33:00	donderdag 17 november 2022	20:54:00
WMS000512327	maandag 14 november 2022	19:00:00	woensdag 16 november 2022	16:15:00
WMS000512328	dinsdag 15 november 2022	14:22:00	donderdag 17 november 2022	12:04:00

Business Hours are:
Monday till Friday 08:00 - 20:00

Saterday 08:00 - 12:00

[lbendlin]

Thanks. Does it need to be DAX or is Power Query ok too?

[RonaldvdH]

@lbendlin Preferably DAX i think

[lbendlin]

see attached. Can be further simplified if you want to avoid creating the MinuteCalendar table.  There will be a performance penalty for large date ranges.

[RonaldvdH]

@lbendlin I'm not sure your solution will work because I have a 1 to many relationship between the date Table and Incident table.

[lbendlin]

The date table has nothing to do with your scenario.  You need a Minutes table, but my last version removed that requirement too.

[RonaldvdH]

@lbendlin in your formula there is a part : GeneratesSeries(0,1439)

However seeing that my data has 2 years worth of dates and if I make a MinuteCalendar that table would have 1.051.200 rows I don't think that does wonders for the performance right ?

Ive already tried change the value tot a 1.051.200 but my laptop is still busy .... hhaha

[lbendlin]

As I said before, a MinuteCalendar is not required any more. Only the intervals are considered (cardinality reduction), so that will be faster.  But you can try materializing that table. There's no good reason to recreate it for every row (other than that the code looks slicker).