cancel
Showing results for
Did you mean:
Post Prodigy

## Why If, else statemet is showing both results?

I have a table of employees requests to take off.  And for the formula below it should filter by John Smith.

Assuming the calculation is for 8/4/2021.  So for 8/4/2021, If John Smith has a blank() enddate, then just return the row that contains the blank enddate, else return the rows that has startdate that is less then or equal to 8/4/2021, and an enddate that is greater then or equal to 8/4/2021.  Here is the formula:

EVALUATE

FILTER(
SUMMARIZE(
Table1,
Table1[RequestNum],
Table1[StartDate],
Table1[EndDate],
Table1[Employee],
),
Table1[Employee] = "John Smith"  &&
IF (
BLANK ()
IN CALCULATETABLE (

DISTINCT (Table1[EndDate]),
ALLEXCEPT (Table1[Employee]),
Table1[EndDate] = BLANK()
),
IF(
AND (
DATEVALUE(Table1[StartDate]) <= DATEVALUE("8/4/2021")
DATEVALUE(Table1[EndDate]) = BLANK()
),

DATEVALUE(Table1[EndDate]) = BLANK()     //Execute if True
,
AND (                                                             //Execute if False
DATEVALUE(Table1[StartDate]) <= DATEVALUE("8/4/2021")
DATEVALUE(Table1[EndDate]) >= DATEVALUE("8/4/2021")
)
),

AND (
DATEVALUE(Table1[StartDate]) <= DATEVALUE("8/4/2021")
DATEVALUE(Table1[EndDate]) >= DATEVALUE("8/4/2021")
)
)
)

However, it returns L573 which is what should have been the only row to return, but it also returns L570, L1595 and P895.

If I take out the Execute if False part of the statement, it returns L573.  And if I take out the Execute if True part of the statement and leaving just the False part, it returns blank.  So I am not sure why when i leave everything as it is, it returns multiple rows as if it executes the true and false values.

returned rows:

1 ACCEPTED SOLUTION
Community Support

Hi @wpf_ ,

Please modify the formula as below.

``````Table 2 =
FILTER (
SUMMARIZE (
Table1,
Table1[RequestNum],
Table1[StartDate],
Table1[EndDate],
Table1[Employee]
),
Table1[Employee] = "John Smith"
&& IF (
BLANK ()
IN CALCULATETABLE (
DISTINCT ( Table1[EndDate] ),
ALLEXCEPT ( Table1, Table1[employee] )
),
Table1[EndDate] = BLANK (),
Table1[startdate] <= DATEVALUE ( "8/4/2021" )
&& Table1[enddate] >= DATEVALUE ( "8/4/2021" )
)
)
``````

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
3 REPLIES 3
Community Support

Hi @wpf_ ,

Please modify the formula as below.

``````Table 2 =
FILTER (
SUMMARIZE (
Table1,
Table1[RequestNum],
Table1[StartDate],
Table1[EndDate],
Table1[Employee]
),
Table1[Employee] = "John Smith"
&& IF (
BLANK ()
IN CALCULATETABLE (
DISTINCT ( Table1[EndDate] ),
ALLEXCEPT ( Table1, Table1[employee] )
),
Table1[EndDate] = BLANK (),
Table1[startdate] <= DATEVALUE ( "8/4/2021" )
&& Table1[enddate] >= DATEVALUE ( "8/4/2021" )
)
)
``````

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Super User IV

@wpf_ , I think a measure like this should help with columns

New meausre =
var _min = DATEVALUE("8/4/2021")
return
calculate( count(Table1[Employee]), filter(Table1, Table1[StartDate] <= _min && (Table1[Enddate] >=Min || isblank(Table1[EndDate]))))

Proud to be a Super User!

Post Prodigy

@amitchandak Why would the count help me in this situation?

Announcements

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.