Forum Discussion
Post PatronUsing a slicer to change values in a formula
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.
7 Replies
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
android1Hi 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")))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
v-qiuyu-msftCommunity Support
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
