Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
android1
Post Patron
Post Patron

Using 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 7
v-qiuyu-msft
Community Support
Community 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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

CheenuSing
Community Champion
Community Champion

@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

   

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

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")))

@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

 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

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.

@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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.