cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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]

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

How about wrapping the result inside a MAX

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.