Power BI DAX Measure works for one column but not another

Question

Using the information below I need to create a new table in DAX called Table (Download a demo file here).

I need to find the location of each employee (column "Name") at the time of the sale date in column "Sale Date" based on their contract details in table DbEmployees. If there is more than one valid contract for a given employee that the sale date fits in, use the shortest contract length.

My problem is that the below measure isn't working to generate column "Location", but it works just fine for column "new value".

Why is this happening and how can it be fixed?

Expected result:

SaleID	EmployeeID	Sale Date	new value	Name	Location
1	45643213	2021-02-04	89067445	Sally Shore	4
2	57647868	2020-04-15	57647868	Paul Bunyon	3
3	89067445	2019-09-24	57647868	Paul Bunyon	6

DbEmployees:

ID	Name	StartDate	EndDate	Location	Position
546465546	Sandra Newman	2021/01/01	2021/12/31	1	Manager
546465546	Sandra Newman	2020/01/01	2020/12/31	2	Clerk
546465546	Sandra Newman	2019/01/01	2019/12/31	3	Clerk
545365743	Paul Bunyon	2021/01/01	2021/12/31	6	Manager
545365743	Paul Bunyon	2020/04/01	2020/05/01	3	Clerk
545365743	Paul Bunyon	2019/04/01	2021/01/01	6	Manager
796423504	Sally Shore	2020/01/01	2020/12/31	4	Clerk
783546053	Jack Tomson	2019/01/01	2019/12/31	2	Manager

DynamicsSales:

SaleID	EmployeeID	Sale Date
1	45643213	2021/02/04
2	57647868	2020/04/15
3	89067445	2019/09/24

DynamicsContacts:

EmployeeID	Name	Email
45643213	Sandra Newman	[email protected]
65437658	Jack Tomson	[email protected]
57647868	Paul Bunyon	[email protected]
89067445	Sally Shore	[email protected]

DynamicsAudit:

SaleID	Changed Date	old value	new value	AuditID	Valid Until
1	2019/06/08	65437658	57647868	1	2020-06-07
1	2020/06/07	57647868	89067445	2	2021-05-07
1	2021/05/07	89067445	45643213	3	2021-05-07
2	2019/06/08	65437658	57647868	4	2020-06-07
2	2020/06/07	57647868	89067445	5	2021-05-07
2	2021/05/07	89067445	45643213	6	2021-05-07
3	2019/06/08	65437658	57647868	7	2020-06-07
3	2020/06/07	57647868	89067445	8	2021-05-07
3	2021/05/07	89067445	45643213	9	2021-05-07

Joao Leal Joao Leal · Accepted Answer · 2021-04-22T11:24:02

From what I can see there are a couple of issues with your formula.

First of all there is no relationship between Table and DbEmployees so when you are filtering exclusively on the dates, which might get you the wrong Location. This can be fixed by changing the formula to:

Location = 
VAR CurrentContractDate = [Sale Date]
VAR empName = [Name]
RETURN
VAR RespLocation =
            TOPN (
                1,
                FILTER(DbEmployees, DbEmployees[Name] = empName),
                IF (
                .....

Secondly, you need to remember that the TOPN function can return multiple rows, from the documentation:

If there is a tie, in order_by values, at the N-th row of the table, then all tied rows are returned. Then, when there are ties at the N-th row the function might return more than n rows.

This can be fixed by picking the Max/Min of the result in the table:

RETURN MAXX(SELECTCOLUMNS( RespLocation,"Location", [Location] ), [Location])

Finally, I don't understand why the last row on the expected result should be a 3, given that the sale date is within a record with location 6.

Full expression:

Location = 
VAR CurrentContractDate = [Sale Date]
VAR empName = [Name]
RETURN
VAR RespLocation =
            TOPN (
                1,
                FILTER(DbEmployees, DbEmployees[Name] = empName),
                IF (
                    CurrentContractDate <= DbEmployees[EndDate]
                        && CurrentContractDate >= DbEmployees[StartDate], //Check, whether there is matching date
                    DATEDIFF ( DbEmployees[StartDate], DbEmployees[EndDate], DAY ), //If so, rank matching locations (you may want to employ a different formula)
                    MIN (  //If the location is not matching, calculate how close it is (from both start and end date)
                        ABS ( DATEDIFF ( CurrentContractDate, DbEmployees[StartDate], DAY ) ),
                        ABS ( DATEDIFF ( CurrentContractDate, DbEmployees[EndDate], DAY ) )
                    ) + 1000000 //Add a discriminating factor in case there are matching rows that should be favoured over non-matching.
                ), 1
            )
            RETURN
            MAXX(SELECTCOLUMNS( RespLocation,"Location", [Location] ), [Location])

Power BI DAX Measure works for one column but not another

1 Answers