Forum Discussion

danextian's avatar
danextian
Icon for Super User rankSuper User
9 years ago

Simulation whether numbers in a column is within the upper limit set on a numeric slicer

Hi All,

 

I entered this formula as a measure 

upperlimit = 
CALCULATE ( MIN ( 'Table'[Days] ), ALLSELECTED ( 'Table'[Days] ) )

 

My goal is to set this  measure as upper limit to simulate whether the values in a column is within or beyond SLA. I'm using a numeric slicer to set the limit. It works fine on a card but not when using this measure in a column.

 

Here is the formula for my column

Within/Beyond = IF (
        'table'[Column] <= upperlimit,
        " within",
        "beyond")

This formula  doesnt work at all. 

When I try to return the value of upperlimit within this column, the result is just plain zero and not the number on the slicer.

Need Help

7 Replies

  • GilbertQ's avatar
    GilbertQ
    Icon for Super User rankSuper User
    9 years ago

    Hi danextian

     

    What about another option where you use the Enter Data and put in a column which will be the value you want for your SLA.

    EG: Column Name: SLA

          Table name: SLA Limit

     

    Then create a measure for your SLA table.

    EG: 

    SLA Number = MIN('SLA Limit'[SLA])


    Then when you create your column instead of using a measure called "UPPER LIMIT" instead you pass the Value from your new table?

    EG: 

    SLA Column = IF('TableName'[Amount] <= [SLA Number],"LOW","HIGHER")
    • danextian's avatar
      danextian
      Icon for Super User rankSuper User
      9 years ago

      Hi GilbertQ,

       

      I think i followed the same approach initially. 

      I created a measure 

      upper limit = calculate(min('SLA TAble'[SLA]),ALLSELECTED(('SLA TAble'[SLA])))

       

      I checked that the measure was working fine. So if I enter 7, the measure when used in a card shows 7. However when i use this measure in a column, it returns  zero. SLA Column = upper limit 

  • v-haibl-msft's avatar
    v-haibl-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    danextian

     

    You need to use calculated measure to get the desired result. You can first duplicate the original Table which only includes Days column. So there will be two same tables (Table and Table2) with one Days column.

     

    Then create two measures with following formula.

    upperlimit = 
CALCULATE ( MIN ( 'Table2'[Days] ), ALLSELECTED ( 'Table2'[Days] ) )

     

     

    Measure =
IF ( CALCULATE ( MAX ( 'Table'[Days] ) ) <= [upperlimit], "within", "beyond" )

     

    Create a table visual in the canvas, drag Days column and Measure into it.

    Create a slicer visual in the canvas, drag 'Table2'[Days] into it.

     

     

    Best Regards,
    Herbert

    • danextian's avatar
      danextian
      Icon for Super User rankSuper User
      9 years ago

      v-haibl-msft,

       

      thanks for that. I've given up on using a calclulate column to achieve my desired result (would have been better if it was possible) and just use measures instead. I should have know that I could not make the values  of calculated column dynamic based on the chosen value from a slicer.