Forum Discussion

android1's avatar
android1
Post Patron
10 years ago

Using a measure in a calculated column

Hi,

 

I have this calc col -> Punctuality = IF (OTIF[ShiftInTime]<OTIF[TimeFrom]-1/24*.25,"Early",

IF (OTIF[ShiftInTime]>OTIF[TimeFrom]+1/24*.25,"Late","On Time"

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

 

I have this measure -> targetperc OT = LASTNONBLANK('OT%'[OT%],0.25) 

 

I substitute the *.25 in th calc col with * targetperc OT to give me -> 

 

Punctuality = IF (OTIF[ShiftInTime]<OTIF[TimeFrom]-1/24*[targetperc OT],"Early",

IF (OTIF[ShiftInTime]>OTIF[TimeFrom]+1/24*[targetperc OT],"Late","On Time"

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

 

I have applied the values in OT% table to a slicer. However, when I select values in the slicer, nothing changes. Below is the table my measure is using.

What am I missing here?

 

 

Need Help

10 Replies

  • BhaveshPatel's avatar
    BhaveshPatel
    Super User
    10 years ago

    Hi

     

    You can not use measure in calculated column. you need to come up with some other logic.

    • android1's avatar
      android1
      Post Patron
      10 years ago

      Parameters? Can you tell me how I can do this?

      • BhaveshPatel's avatar
        BhaveshPatel
        Super User
        10 years ago

        Can you please provide me a snapshot your dataset and I would be more than happy to provide you exact solution.

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    10 years ago

    android1

     

    Thanks for uploading the pbix.

    Two things.

    1. The targetperc OT is using the OTP% table, if it is using OT%, please change it.
      targetperc OT = LASTNONBLANK('OTP%'[OTP%],0.25)
    2. Not a calculated column Punctuality, use the expression in filters. For example as below. You can modify other measures where the Punctuality is involved accordingly. By this approach, the measure values vary according to the Slicer.
       
      Early =
COUNTROWS (
    FILTER (
        OTIF,
        IF (
            OTIF[ShiftInTime]
                < OTIF[TimeFrom]
                - 1 / 24
                * [targetperc OT],
            "Early",
            IF (
                OTIF[ShiftInTime]
                    > OTIF[TimeFrom]
                    + 1 / 24
                    * [targetperc OT],
                "Late",
                "On Time"
                    & IF ( OTIF[ShiftInTime] > OTIF[TimeFrom] + 1 / 24 * 1, "Perfect" )
            )
        )
            = "Early"
    )
)