Forum Discussion
Helper IIIIF statement maybe?
Hi tmears
From my point of view ...
if you know and can assign to each customer a range hour.
Let's say all the customers that end their hours at 17:00 could be assigned 'Type A', and all those that end their hours at 18:00 could be assigned 'Type B'
Then you could use ....
FirstDayEndTime =
IF(
Customers[Type] = "Type A",
DATE ( YEAR ( incident[createdon] ), MONTH ( incident[createdon] ), DAY ( incident[createdon] ) )
& " 17:00:00",
DATE ( YEAR ( incident[createdon] ), MONTH ( incident[createdon] ), DAY ( incident[createdon] ) )
& " 18:00:00"
)
Hope That Helps
VicenteHi tmears,
In addition, using SWITCH function should also work. :smileyhappy:
FirstDayEndTime = SWITCH ( Customers[Type], "Type A", DATE ( YEAR ( incident[createdon] ), MONTH ( incident[createdon] ), DAY ( incident[createdon] ) ) & " 17:00:00", "Type B", DATE ( YEAR ( incident[createdon] ), MONTH ( incident[createdon] ), DAY ( incident[createdon] ) ) & " 18:00:00", "Type C", DATE ( YEAR ( incident[createdon] ), MONTH ( incident[createdon] ), DAY ( incident[createdon] ) ) & " 19:00:00", "Type D", DATE ( YEAR ( incident[createdon] ), MONTH ( incident[createdon] ), DAY ( incident[createdon] ) ) & " 20:00:00" )Regards
Helper IIIthanks for the reply, sorry you are right not alot of detail:
i have followed the enlcosed post:
basically i am trying to calcualte the amount of time a service request or case is open for in dynamics. the above posts works brilliantly however the issue i have is that swome customer have different support hours. following the post the support hours is 9:00 to 17:00 but some specfic customer have different hours for example 8:00 to 18:00 so the time calcuations on these customer are wrong. Not sure if possible and have been trying to wrap my brians that if the case relates to customer A the oprnbing hours are changed
Microsoft EmployeeHi tmears,
In addition, using SWITCH function should also work. :smileyhappy:
FirstDayEndTime =
SWITCH (
Customers[Type],
"Type A", DATE ( YEAR ( incident[createdon] ), MONTH ( incident[createdon] ), DAY ( incident[createdon] ) )
& " 17:00:00",
"Type B", DATE ( YEAR ( incident[createdon] ), MONTH ( incident[createdon] ), DAY ( incident[createdon] ) )
& " 18:00:00",
"Type C", DATE ( YEAR ( incident[createdon] ), MONTH ( incident[createdon] ), DAY ( incident[createdon] ) )
& " 19:00:00",
"Type D", DATE ( YEAR ( incident[createdon] ), MONTH ( incident[createdon] ), DAY ( incident[createdon] ) )
& " 20:00:00"
)
Regards
vcastelloResolver III
Hi all,
Obviusly this is a more elegant solution. Specially if you have more than 2 types of customers ...
- tmears
you guys are brilliant. one final questiopn i hope i now get the following error:
Column '' in Table '' contains blank values and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.
- vcastello
Hi tmears
1.- The table where each customer is assigned to a 'type' has to be a new one.
2.- It has to have a 'customer' column and a 'type' column (along with any other column you might need).3.- It can't have duplicates. Why do you need them? every customer has a end hour so ... NO need form duplicates.
4.- You have to create a relationship between this table and the one where you have all the data (fact table).
You can create that 'customer table' in excel and import it to your model.
Hope that helpsVicente