Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Create columns based on multiple filters

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

Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    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.

3 Replies

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    4 years ago

    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's avatar
    smpa01
    Community Champion
    4 years ago

    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's avatar
      smpa01
      Community Champion
      4 years ago

      Anonymous  did you give this a try yet?