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.
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:
Solved! Go to Solution.
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
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
@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]))))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
94 | |
77 | |
63 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |