Create columns based on multiple filters

[Anonymous]

Hi 

I have 2 tables "Employees" and "Leave" having relations many to many based on EmpID 

Employee

EmpID

Date

Status

Leave

EmpID

StartDate

EndDate

I have to create the column which will check if employee id is not in Leave table or if employee is present in leave table then the date of Employee table should not between the StartDate and EndDate of leave table for that employee

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous ,

Is that you want create a column in Employee Table?

If yes, please try this code to create a column:

CHECK =
VAR _if_in_enddate =
    CALCULATE(
        MAX( 'Leave'[EndDate] ),
        FILTER( 'Leave', [EmpID] = EARLIER( Employee[EmpID] ) )
    )
VAR _if_in_startdate =
    CALCULATE(
        MIN( 'Leave'[StartDate ] ),
        FILTER( 'Leave', [EmpID] = EARLIER( Employee[EmpID] ) )
    )
VAR _if_between =
    IF(
        AND( [Date] > _if_in_startdate, [Date] < _if_in_enddate ),
        "Between Startdate and Enddate",
        "Not Between"
    )
RETURN
    IF( ISBLANK( _if_in_enddate ), "Not in Leave", _if_between )

Result:

If i misunderstood you, please share some data without sensitive data and expect result.

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[smpa01]

@Anonymous  you can use a measure like this. Bring Axis from Employee and drop this measure.

Measure2 =
CALCULATE (
    MAX ( Leave[EmpID] ),
    VAR _base =
        NATURALINNERJOIN (
            SELECTCOLUMNS (
                Leave,
                "emp", Leave[EmpID] & "",
                "_start", Leave[StartDate],
                "_end", Leave[EndDate]
            ),
            SELECTCOLUMNS ( Employee, "emp", Employee[EmpID] & "", "dt", Employee[Date] )
        )
    VAR _left =
        SUMMARIZE ( _base, [emp], [_start], [_end] )
    VAR _right =
        SUMMARIZE (
            FILTER ( _base, [_start] <= [dt] && [dt] <= [_end] ),
            [emp],
            [_start],
            [_end]
        )
    RETURN
        EXCEPT ( _left, _right )
)

[smpa01]

@Anonymous  did you give this a try yet?