Using a slicer to change values in a column

[android1]

Hi,

I have a calculated column which returns the number of times someone was on time for an appointment ->

On Time =
CALCULATE(COUNTROWS ( FILTER ( OTIF, OTIF[ShiftInTime] = OTIF[TimeFrom]
)))

I want to know how many times someone was 5/10/15/20/30 mins either side of appointment time. [TimeFrom] is

the appointment time, [ShiftInTime] is the time the person was there.

I have manually added a new table OT% with a column containing values for the slicer.

I have a measure targetperc OT = LASTNONBLANK('OT %'[OT %],1) and have applied it to above calculated column to give me -> On Time =
CALCULATE(COUNTROWS ( FILTER ( OTIF, OTIF[ShiftInTime] = OTIF[TimeFrom]*[targetperc OT]
)))

How do I determine if for example [TimeFrom] is 14:00  [ShiftInTime] is 14:05. In this case the person is within 5 mins. 13:55 would also be within 5 mins.

.

[v-shex-msft]

Hi android1,

Based on my understanding, you want to get the ‘on time’ records which equal or less than the choice data in slicer, right?

You could follow below steps:

1. Create a test table.

 

1. Add a calculate column to get the datediff of ‘ShiftInTime’ and ’TimeFrom’ column.

Dax: Range = ABS((HOUR([TimeFrom])*60 + MINUTE([TimeFrom]))-(HOUR([ShiftInTime])*60+MINUTE([ShiftInTime])))

1. Add a calculate column to get the within range.

Dax:

WithIn = if([Range]<=5,5,

IF([Range]<=10,10,

if([Range]<=15,15,

IF([Range]<=20,20,

if([Range]<=25,25,

IF([Range]<=30,30,

IF([Range]<=35,35,

if([Range]<=40,40,

IF([Range]<=45,45,50)))))))))

 

1. Add a measure to calculate the count of specify rows.

Dax: On time = CALCULATE(COUNT('Out Time Record'[Range]), Filter (ALL( 'Out Time Record'), COUNTX(FILTER( 'Out Time Record', 'Out Time Record'[WithIn] >= Earlier ('Out Time Record'[WithIn] ) ),'Out Time Record'[Range])))

1. Create the reports.

Slicer:

 

Table:

 

Card:

Result:

Notice: I drag the within to the slicer, the table displayed the current rows, the card view show the count of match records.

If above is not help, please provide more detail info and feel free to let me know.

Regards,

Xiaoxin Sheng

[android1]

Hi,

Yeah, that's what I'm looking for. What I need to also show is a count of appointments in these ranges when i put the measure into my table.

In pic below, I need to show 123K split by Region

[Greg_Deckler]

I don't know the data types of your columns, but what I imagin you would do would be to get [Time From] and [ShiftInTime] to numeric values so that 14:00 becomes 1400. Then you could create a column like:

MinutesOffAbs = ABS([NumTimeFrom] - [NumShfitInTime]

Then you could normalize this with a big ugly IF statement like:

MinutsOffAbsNorm = IF([MinutesOffAbs]<5,0,IF([MinutesOffAbs]<10,5,10))

Obviously, add in all the extra nested IF's as necessary. Then, use this column in your slicer.

[android1]

Hi smoupre,

Having trouble making that ugly IF statement. How do I continue it on

?

MinutsOffAbsNorm = IF([MinutesOffAbs]<5,0,IF([MinutesOffAbs]<10,5,10))

Trying 

MinutsOffAbsNorm = IF([MinutesOffAbs]<5,0,IF([MinutesOffAbs]<10,5,10,IF([MinutesOFFAbs] <15, 10,15)))

but getting error. Can I keep adding like this?

[v-shex-msft]

Hi android1,

>>but getting error. Can I keep adding like this?

You can refer to below formalu:

if(logic, value if true, if(logic, value if true,  if(logic, value if true, value if false) )

My previous reply

WithIn = if([Range]<=5,5,

IF([Range]<=10,10,

if([Range]<=15,15,

IF([Range]<=20,20,

if([Range]<=25,25,

IF([Range]<=30,30,

IF([Range]<=35,35,

if([Range]<=40,40,

IF([Range]<=45,45,50)))))))))

Reference:

IF Function (DAX)

Regards,

Xiao xin Sheng