Equivalent of SQL Inner Query in DAX for Employee Mutation

[pelangi]

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?

[v-jingzhang · Accepted solution]

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.

[amitchandak]

@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]

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.