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

[danextian]

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.

[v-haibl-msft]

@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]

@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. 

[GilbertQ]

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]

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 

[GilbertQ]

Hi @danextian

What is the reason for using the ALLSELECTED?

[danextian]

While a calculated column accepts ALLSELECTED, unfortunately, it doesn't do anything. It doesn't show the value that is within speicified filters. 😞

[danextian]

If I dont use ALLSELECTED, the measure will not respect my filter so it will show the lowest value in the table regardless of the filter for Days.  If i use all, the lowest value will always be shown regardless of the value selected from the slicer.