cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
android1
Post Patron
Post Patron

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?

 

OT Table.jpg

 

10 REPLIES 10
Eric_Zhang
Microsoft
Microsoft

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

 

BhaveshPatel
Community Champion
Community Champion

Use of Parameters would be more appropriate solution in your case. 

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

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

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

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

Here's my file. I had a go at adding a parameter and applying the values in my OT% table to it. Parameter name is OTP%.

 

 

https://www.dropbox.com/s/f9c12ioi82vh0zh/OTIF%20Extra%20Columns%20IF%20Slicer%20Final%20Last.pbix?d...

Hi Android1,

 

Firstly you have to create parameter ( list of values) based on your IF% Table.

This can be done in powerquery mode. Pass on all the list of the values of IF% Table.

 

create a new custom column,

 

Pass on your formula you created before and replace the value of 0.25 with Parameter name.

 

This will let you choose the value later on dynamically and you can select the value of your choice.

 

Hope this will clarify your problem. I was unable to create this for you because of connection settings.

Parameter table creationParameter table creationPassing onto the Parameters to your custom columnPassing onto the Parameters to your custom column

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

You have to slightly change the formula for custom column as PowerQuery works on IF logic but have to add else statment too.

 

Let me know if you get stuck further.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
BhaveshPatel
Community Champion
Community Champion

Hi

 

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

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

Ah, ok. Thank you.

diego_salinas
New Member

Try changing the column data format to "general".

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors