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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

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
MattAllington
Community Champion
Community Champion

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.
I will not give you bad advice, even if you unknowingly ask for it.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors