Forum Discussion

RonaldvdH's avatar
RonaldvdH
Post Patron
2 years ago

Function Related doesn't seem to work

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   Doorloopt...
Need Help
RonaldvdH's avatar
RonaldvdH
Post Patron
2 years ago

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's avatar
isjoycewang
Solution Supplier
2 years ago

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's avatar
    RonaldvdH
    Post Patron
    2 years ago

    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's avatar
      RonaldvdH
      Post Patron
      2 years ago

      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's avatar
        RonaldvdH
        Post Patron
        2 years ago

        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 ?