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

 

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

 

Need Help

20 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

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

    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'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 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

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    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