Using a slicer to change values in a formula

[android1]

Hi,

The following calc column contains a value of .25 -> Punctuality = IF (OTIF[ShiftInTime]<OTIF[TimeFrom]-1/24*.25,"Early",IF (OTIF[ShiftInTime]>OTIF[TimeFrom]+1/24*.25,"Late","On Time" &

(OTIF[ShiftInTime]>OTIF[TimeFrom]+1/24*1,"Perfect")))

This measure -> OT(+15-15) = COUNTROWS ( FILTER ( OTIF, OTIF[Punctuality] = "On Time" ) ) uses the above and tells me how many calls were On Time.

How can I use a slicer to select a value which takes the place of .25? I want to replace .25 by selecting a value that I add to a slicer.

[v-qiuyu-msft]

Hi @android1,

There are something wrong in the calculated column formula:

Punctuality = IF (OTIF[ShiftInTime]<OTIF[TimeFrom]-1/24*.25,"Early"​,IF (OTIF[ShiftInTime]>OTIF[TimeFrom]+1/24*.25,"Late",​"On Time" &

(OTIF[ShiftInTime]>OTIF[TimeFrom]+1/24*1,"Perfect"​)))

Because IF() function syntax is IF(logical_test>,<value_if_true>, value_if_false). It should be like this:

Punctuality = IF (OTIF[ShiftInTime]<OTIF[TimeFrom]-1/24*.25,"Early",IF(OTIF[ShiftInTime]>OTIF[TimeFrom]+1/24*.25,"Late",IF(OTIF[ShiftInTime]>OTIF[TimeFrom]+1/24*1,"Perfect","On Time")))

In your scenario, I would suggest you share some sample data and screenshots about desired results, so we can understand it better.

If you have any question, please feel free to ask.

Best Regards,
Qiuyun Yu

[android1]

Ok,

I've now got the measures On Time = COUNTROWS(FILTER(OTIF, [ShiftInTime]=[TimeFrom]*[targetperc OT] || [ShiftInTime]<> [TimeFrom]*[targetperc OT]))

targetperc OT = LASTNONBLANK('OT %'[OT %],0.25)

I'm using a sclicer to select the [target OT] value but it's not changing the values in measure On Time.

Here's my file -> https://www.dropbox.com/s/6ajjnyotics8vx9/OTIF%20Slicer%20Dilemma%20V2.0.pbix?dl=0

 Look at first tab 'OTIF by Region

[Anonymous]

@android1

1. Assume you have table called SelectTable and a column named SelectValue and this  column is used in a Slicer display.

2. Based on the value in the Slicer to alter the calculation make use of the function FIRSTNONBLANK

3.  For your example pasted  modify

Punctuality = IF (OTIF[ShiftInTime]<OTIF[TimeFrom]-1/24*.25,"Early",IF (OTIF[ShiftInTime]>OTIF[TimeFrom]+1/24*.25,"Late","On Time" &

(OTIF[ShiftInTime]>OTIF[TimeFrom]+1/24*1,"Perfect")))

by

Punctuality = IF (OTIF[ShiftInTime]<OTIF[TimeFrom]-1/24*.25,"Early",IF (OTIF[ShiftInTime]>OTIF[TimeFrom]+1/24*(FirstNonblank(SelectTable[SelectValue] ,1) ) , "Late","On Time" &

(OTIF[ShiftInTime]>OTIF[TimeFrom]+1/24*1,"Perfect")))

This assumes there is always a value selected in the Slicer.  If it is not going to be the case then we need to alter this with

to check if a value is selected in the slicer.

If ( HasONEValue(SelectTable[SelectValue] )
ActualFormula as above using FIRSTNONBlank, Defaultformula ) 

Try it out and if it works please accept this as a solution and also give Kudos.

Cheers

CheenuSing

[android1]

Hi CheenuSing,

I'm getting the error message 'A table of multiple values was supplied where a single value was expected' in the Punctuality formula.

I have created the slicer using a table called IF % and a column IF %.

My formula is Punctuality = IF (OTIF[ShiftInTime]<OTIF[TimeFrom]-1/24*.25,"Early",IF (OTIF[ShiftInTime]>OTIF[TimeFrom]+1/24*(FirstNonblank('IF %'[IF %] ,1) ) , "Late","On Time" &
(OTIF[ShiftInTime]>OTIF[TimeFrom]+1/24*1,"Perfect")))

[Anonymous]

@android1

What I had shown is the way to create a meassure using the value in the slicer.

Apparently you want to use the value in the filter in a caculated column, which means you want to do this as a part of the source table.

1. Create measure called Whatif = If ( HASONEVALUE('IF %'[IF %]) ,FIRSTNONBLANK('IF %'[IF %] ,1) ,0.25)

     What this means if a value has been selected  use that value or else use default vaule 0.25  

2. Use this in the column formula replacing the FirstNonblank('IF %'[IF %] ,1) with [Whatif].  This should work.

3. Try it out and let me know

[android1]

Hi,

This seems to be working the first time I select a value from the slicer (The values in my table change). 

When I select a different value in the sclicer, nothing changes. The circular timer appears on my table which shows it's recalculating

the values in my table but the values never change.

[Anonymous]

@android1

Calculated columns get refreshed only once  and therefore we are not able to see the change on different selections.

Is it possible to convert the column to a measure and then display the same.

Can you share some sample data and the visual you are expecting.

Cheers

CheenuSing