Forum Discussion
Function Related doesn't seem to work
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 ?
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 ?
RELATED should not be used in measures, only in calculated columns.
