Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Proud to be a Super User!
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
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.
Proud to be a Super User!
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")
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
Proud to be a Super User!
While a calculated column accepts ALLSELECTED, unfortunately, it doesn't do anything. It doesn't show the value that is within speicified filters. 😞
Proud to be a Super User!
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.
Proud to be a Super User!
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |