DAX calculated column only working when variables used

[rbbi]

I'm trying to generate data using DAX tables and columns. Perhaps not the ideal way to do this, but helpful in my situation. I just don't understand why my calculated column doesn't work unless I assign a VARiable with a value from the current row for StoreName.

The PBIX file can be downloaded here: https://github.com/RodAtBurkeDataConsulting/PowerBI/blob/master/Roster.pbix

Here's the tables:

Dates: a list of dates
Stores: a list of stores

Roster: a list of employees and date ranges they will be working at each store

RosterList: CrossJoin of Dates and Stores, then add a calculated column to show the employees at each store on each date

I'm not defining any relationships, just using DAX.

The DAX is pretty simple:

Dates = CALENDAR("1 Jan 2020","10 Jan 2020")

Stores = DATATABLE("StoreName", STRING, {{"Fish'n'Chips"},{"Burger Joint"}})

Roster = DATATABLE("Employee", STRING, "StoreName", STRING, "StartDate", DATETIME, "EndDate", DATETIME,
{
    {"Jane", "Burger Joint", "1 Jan 2020", "3 Jan 2020"},
    {"Jane", "Fish'n'Chips", "2 Jan 2020", "4 Jan 2020"},
    {"Fred", "Fish'n'Chips", "2 Jan 2020", "2 Jan 2020"},
    {"Fred", "Burger Joint", "6 Jan 2020", "9 Jan 2020"}
})

RosterList = 
SELECTCOLUMNS(
    CROSSJOIN(Dates, Stores),
    "Sort", Stores[StoreName] & " " & FORMAT(Dates[Date],"yyyyMMdd"), // just for sorting the display
    "Date", Dates[Date],
    "StoreName", Stores[StoreName]
    )​

Next I add a calculated column that finds the employee (or employees) who are rostered on for each date, based on the date range in the roster table. Note that relationships or LOOKUP() can't be used because (a) the "join" is based on two columns, not one, and (b) the join is based on the single Date value from the RosterList table falling within the range of dates on each roster row. SO, I'm using FILTER() to filter the Roster table to get rows matching the store and where Dare is within the Roster row's date range, and CONCATENATEX() to join values together if there's more than one.

Here's my first attempt at the calulated column that gives the wrong results:

Rostered Employee WRONG = 
CONCATENATEX(
    SELECTCOLUMNS(
        FILTER(Roster,
            [StoreName] = Roster[StoreName]
            && [Date] >= Roster[StartDate]
            && [Date] <= Roster[EndDate]
        )
        , "Employee"
        , [Employee]
    ),
    [Employee],
    ", "
)

But this gives the wrong results, with Jane appearing mulitple times on the same row, and Fred appearing in Fish'n'Chips on days where he's only rostered for Burger Joint and other errors shown here:

Now, if I make one small change ... assign [StoreName] to a VAR and use that in the FILTER, it gives the correct values ...

Rostered Employee with VARs = 
VAR s = [StoreName]
RETURN
CONCATENATEX(
    SELECTCOLUMNS(
        FILTER(Roster,
            s = Roster[StoreName]
            && [Date] >= Roster[StartDate]
            && [Date] <= Roster[EndDate]
        )
        , "Employee"
        , [Employee]
    ),
    [Employee],
    ", "
)

I just don't understand why assigning [StoreName] to a VAR is any different to using [StoreName] in the filter.

I thought [StoreName] on with no table qualification should be referencing the current row's value of StoreName??

Hoping someone can explain this?

thanks!

[rbbi · Accepted solution]

Well, I have solved the problem myself just a short time after posting!

In my first attempt at the calculated column, I used [StoreName], but the problem is this unqualified column name is ambiguous and could refer to either Roster[StoreName] or RosterList[StoreName]. I was assuming that because this was in a calculated column expression, it would default to the "current" table (which I guess is true when it's assigned to a VAR outside of the FILTER function) ... i.e. the implicit row context of the calculation. This seems to be wrong.

The simple fix is to qualify the column reference (also for [Date] though it's not ambiguous, but for clarity it should be qualified!)

Rostered Employee = 
CONCATENATEX(
    SELECTCOLUMNS(
        FILTER(Roster,
            // Wrong ... [StoreName] = Roster[StoreName]
            // Right ...
            RosterList[StoreName] = Roster[StoreName]
            && RosterList[Date] >= Roster[StartDate]
            && RosterList[Date] <= Roster[EndDate]
        )
        , "Employee"
        , [Employee]
    ),
    [Employee],
    ", "
)

This is now giving the correct results! Thanks for anyone who viewed it so far, and hopefully this may help others who make the same mistake that I did 🙂