If command with new measure and column in table

[Anonymous]

Hi All,

I'm quite new to Power BI and have a question regarding the if command. 

I created a new measure with DATEDIFF and called this 'difference time'. Right now, I want to use a new measure that will measure when the ''difference time'' is bigger than ''appointment.type,duration'' which is a column in my table.

I tried to do this with the following command:

Uitloop = IF('studenten'[difference time] > 'studenten'[appointment.type.duration], "yes", "no")

However, it doesn't recognize the appointment type duration (see the attached screenshot).

Any help will be appreciated! Thank you in advance!

[Anonymous]

Hi @VahidDM and @ValtteriN,

Thank you for the reply. It works when I use the Max or Sum aggregate, however, if I want to add the measure to my table to see which ones go over the predicted appointment type duration, it says it can't show the visual.

This happens when I use either Max or Sum, or any other aggregate. 

Thank you agian for your help!

[VahidDM]

@Anonymous 

It because the type of your column is Text, change it to whole or decimal number.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[Anonymous]

Sample data: 

Execution.start.time	Execution.end.time	Appointment.type.duration
10:31:00	12:12:00	00:35:00
10:48:00	12:12:00	01:00:00
12:47:00	13:56:00	00:35:00
06:41:00	07:48:00	00:40:00
12:17:00	13:12:00	01:00:00
11:38:00	12:33:00	00:40:00
07:45:00	08:13:00	00:35:00
08:44:00	09:09:00	00:35:00

First I found the difference in time between the execution start time and execution end time by using a measure:

Difference time = DATEDIFF(MIN(studenten[execution start time]),Max(studenten[execution end time]),MINUTE)

This gave me the following result:

Execution.start.time	Execution.end.time	Appointment.type.duration	Difference time
10:31:00	12:12:00	00:35:00	101
10:48:00	12:12:00	01:00:00	84
12:47:00	13:56:00	00:35:00	69
06:41:00	07:48:00	00:40:00	67
12:17:00	13:12:00	01:00:00	55
11:38:00	12:33:00	00:40:00	55
07:45:00	08:13:00	00:35:00	28
08:44:00	09:09:00	00:35:00	25

Now I want to find when the ''difference in time'' is bigger than the appointment.type.duration, when this is the case I want it to say ''yes'' if this is not the case I want it to say ''no''. In the example I have added it manually (in red), but actually want to use a formula to automatically get this. Which I tried to do with the IF command. 

Execution.start.time	Execution.end.time	Appointment.type.duration	Difference time	Uitloop
10:31:00	12:12:00	00:35:00	101	yes
10:48:00	12:12:00	01:00:00	84	yes
12:47:00	13:56:00	00:35:00	69	yes
06:41:00	07:48:00	00:40:00	67	yes
12:17:00	13:12:00	01:00:00	55	no
11:38:00	12:33:00	00:40:00	55	yes
07:45:00	08:13:00	00:35:00	28	no
08:44:00	09:09:00	00:35:00	25	no

[Anonymous]

If I do this, it changes everything to 0. 

Before it showed

Appointment.type.duration

00:40:00 --> meaning the predicted duration of the appointment is 40 minutes. When i change the type to a whole number it changes it to a 0. 

I'll try to post a sample dataset for you to take a look at! 

Thank you again so much for the help. 

[ValtteriN]

Hi,

SUM doesn't work with text but MAX will return values anyway. Also instead of whole number you can use decimal number data type. 

[Anonymous]

Hi,

If I try to change it to a decimal number it gives me an error, and the visual still doesn't work with Max.

[Anonymous · Accepted solution]

Hi @Anonymous ,

Here you want to compare [Difference time] measure which is in Whole number type with [Appointment.type.duration] column which is in Time type. So you will get error in your visual. You need to convert Time to whole number or convert whole number to Time by calculate and then compare them.

Here I convert [Difference time] to Time type and then compare them.

My Code.

Uitloop = 
VAR _HOUR =
    QUOTIENT ( [Difference time], 60 )
VAR _MINUTES =
    MOD ( [Difference time], 60 )
VAR _Difference_time =
    TIME ( _HOUR, _MINUTES, 00 )
RETURN
    IF (
        _Difference_time > MAX ( studenten[Appointment.type.duration] ),
        "Yes",
        "No"
    )

Result is as below.

You can download my attatch sample file to learn more details.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ValtteriN]

Hi,

You can't directly compare measure and column between each other within a new measure. To solve this you can use functions like MAX or SELECTEDMEASURE to refrer to your column. E.g. IF([Difference time]>MAX('studenten'[appoinment type duration], "Yes","No")

I hope this helps to solve your issue and if it does consider accepting this post as a solution and giving it a thumbs up!

[VahidDM]

Hi @Anonymous 

If you reference a column in a measure, you must wrap the column in an aggregation function like MIN, MAX, SUM, AVERAGE, etc.
So use this:

Uitloop = IF('studenten'[difference time] > sum('studenten'[appointment.type.duration]), "yes", "no")

or 
Uitloop = IF('studenten'[difference time] > sum('studenten'[appointment.type.duration]), "yes", "no")

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/