IF statement maybe?

[tmears]

Hi all

I have a problem wondering if anyone could make any suggestions or help?? 

I have the following calcuated column:

FirstDayEndTime =
DATE ( YEAR ( incident[createdon] ), MONTH ( incident[createdon]  ), DAY ( incident[createdon]  ) )
& " 17:00:00"

the problem i have is that different customers have different end times, could i do a IF statement on a customer name to generate a different end time specific to individual customers?  or any other suggestions??

[vcastello · Accepted solution]

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

Vicente

[vcastello · Accepted solution]

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

Vicente

[Greg_Deckler]

If you created that as a measure, you could put it into a table along with customer name and it should give you what you want. That being said, not sure what you want as there is not a lot of context here.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[tmears]

thanks for the reply, sorry you are right not alot of detail:

i have followed the enlcosed post:

https://community.powerbi.com/t5/Desktop/Calculate-Date-and-Time-difference-considering-the-weekends...

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

[v-ljerr-msft · Accepted solution]

Hi @tmears,

In addition, using SWITCH function should also work. 

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

[vcastello]

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 helps

Vicente

[tmears]

thanks for everyone assisitnace, you are brilliant and have help so much!!

[vcastello]

Hi @tmears

You're welcome

Vicente