cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Patron

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

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

20 REPLIES 20
Super User

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 (
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``````

Super User

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.

Super User

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

Post Patron

@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

Post Patron

@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

Super User

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.

Post Patron

@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])``````

Post Patron

@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 ?

Post Patron

@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

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 ?

Super User

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

Post Patron

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

Super User

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

Post Patron

@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

Monday till Friday 08:00 - 20:00

Saterday 08:00 - 12:00

Super User

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

Post Patron

@lbendlin Preferably DAX i think

Super User

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.

Post Patron

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

Super User

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

Post Patron

@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

Super User

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).

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors