Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
danextian
Super User
Super User

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.






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.
7 REPLIES 7
v-haibl-msft
Employee
Employee

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

 

2017-04-20_180922.jpg

 

Best Regards,
Herbert

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






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.
GilbertQ
Super User
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") 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

Hi @danextian


What is the reason for using the ALLSELECTED?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

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.






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.