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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Niiru1
Helper V
Helper V

Insert a limit in a measure to avoid below 0 or above 100

I have a 3 measures related to absence:

 

1 Calculate number of working days in month - (2.5 days leave allowed)

Measure 5.1 =

VAR _Month = CALCULATE(COUNT(DimDate[Days in Month]),FILTER(DimDate,'DimDate'[WorkingDays] <>BLANK()))

VAR _lastday=
COUNTROWS(
FILTER(DimDate,'DimDate'[WorkingDays] <>BLANK()))-2.5

RETURN
IF ( ISBLANK ( [AbsentByDay] ), BLANK (), _lastday )
 
2. Number of absence days from dates : [AbsentbyDay]
 
3. Measure 1 and Measure 2 subtracted
Measure 5.5 =

VAR _Month = CALCULATE(COUNT(DimDate[Days in Month]),FILTER(DimDate,'DimDate'[WorkingDays] <>BLANK()))

VAR _lastday=
COUNTROWS(
FILTER(DimDate,'DimDate'[WorkingDays] <>BLANK()))-2.5

RETURN
IF ( ISBLANK ( [AbsentByDay] ), BLANK (), _lastday )-[AbsentByDay]
 
Working date - absence LIMIT.PNG
 
My question is : is there a way to limit the NET values in table 3 from going below 0 / over 100 
 
Thanks in advance, apologies if I haven't explained this properly.
 

File attached to help

https://www.dropbox.com/s/u5ztxrg4g2g5qpi/Sample3%28updated%29.pbix?dl=0

1 REPLY 1

How about wrapping the result inside a MAX

 

MAX(answer that may be negative,0)

 

if it is negative, you get zero instead



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors