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 c...
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.

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

Anonymous  did you give this a try yet?