Forum Discussion

pelangi's avatar
pelangi
Helper I
4 years ago
Solved

Equivalent of SQL Inner Query in DAX for Employee Mutation

So the problem is this. Every week I have to create a report about the position of each employee in each department. So basically I record them in Excel with this format.

Field1 : CurrentWeekNumber (Text)
Field2 : EmployeeName (Text)
Field3 : Department (Text)

That activity will surely produce a lot of redundant values in all fields, especially when no one actually mutate to other departments. But in the report, it should always be written.

So I want to replicate this concept by using DAX. I only want to enter once and let DAX generate the report by utilizing "CrossJoin" concept, between week numbers and employee names. So assume I have an employee table which track employee mutation, like this:


Table Name : Employee

Field1 : Name (Text)
Field2 : Department (Text)
Field3 : StartDate (Datetime)
Field4 : FinishDate (Datetime)


Table Name : YearWeek

Field1 : YearWeek (Text)



How do I produce the green table with DAX, if I only have Orange table and Blue table as input for DAX ?

Of course, the green table is just for illustrative only. The real repot will show from W01 to W52 for the entire year. I've used cross join function, but then I got stuck. In SQL, I can write inner query to compare current week with Start Date and Finish Date. But in DAX, it's very confusing.

Can anyone help?

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi pelangi 

     

    You can try this measure. If the employee are in two positions in a week (when the start date is not a week start date), it will display both positions in that week. _previousPosition and _nextPosition variables represent the two positions. You can modify the returned result per your need. 

    Department Position =
VAR _weekStartDate = MIN ( 'Date'[Date] )
VAR _weekEndDate = MAX ( 'Date'[Date] )
VAR _onlyPosition =
    MAXX (
        FILTER (
            Employee,
            Employee[StartDate] <= _weekStartDate
                && (
                    _weekEndDate <= Employee[FinishDate]
                        || ISBLANK ( Employee[FinishDate] )
                )
        ),
        Employee[Department]
    )
VAR _previousPosition =
    MAXX (
        FILTER (
            Employee,
            _weekStartDate <= Employee[FinishDate]
                && _weekEndDate > Employee[FinishDate]
        ),
        Employee[Department]
    )
VAR _nextPosition =
    MAXX (
        FILTER (
            Employee,
            _weekStartDate < Employee[StartDate]
                && _weekEndDate >= Employee[StartDate]
        ),
        Employee[Department]
    )
RETURN
    IF (
        NOT ( ISBLANK ( _previousPosition ) ),
        _previousPosition & "," & _nextPosition,
        _onlyPosition
    )

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    pelangi , If the week is based on the start date, first create the week in the same format

     

    YearWeek = Format([Start date], "YYYY-\W") & format(weeknum([Start Date],2) ,"00")

     

    Create a join in Model view and then use in visual

    • pelangi's avatar
      pelangi
      Helper I
      4 years ago

      Could you elaborate on the "join in Model view" ?

      Even though I've changed "StartDate" and "FinishDate" to week format, these 2 fields indicate the starting week and the ending week. They cannot be linked using conventional table relationship, with "YearWeek" table, in the model view. Unless there's a feature in the model view, which I do not know.

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    4 years ago

    Hi pelangi 

     

    You can try this measure. If the employee are in two positions in a week (when the start date is not a week start date), it will display both positions in that week. _previousPosition and _nextPosition variables represent the two positions. You can modify the returned result per your need. 

    Department Position =
VAR _weekStartDate = MIN ( 'Date'[Date] )
VAR _weekEndDate = MAX ( 'Date'[Date] )
VAR _onlyPosition =
    MAXX (
        FILTER (
            Employee,
            Employee[StartDate] <= _weekStartDate
                && (
                    _weekEndDate <= Employee[FinishDate]
                        || ISBLANK ( Employee[FinishDate] )
                )
        ),
        Employee[Department]
    )
VAR _previousPosition =
    MAXX (
        FILTER (
            Employee,
            _weekStartDate <= Employee[FinishDate]
                && _weekEndDate > Employee[FinishDate]
        ),
        Employee[Department]
    )
VAR _nextPosition =
    MAXX (
        FILTER (
            Employee,
            _weekStartDate < Employee[StartDate]
                && _weekEndDate >= Employee[StartDate]
        ),
        Employee[Department]
    )
RETURN
    IF (
        NOT ( ISBLANK ( _previousPosition ) ),
        _previousPosition & "," & _nextPosition,
        _onlyPosition
    )

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.