Calculated Lookup Column with dates and other filters

[TSchmidt]

Hello,

I currently try to create a calculated column where multiple filters are applied.

My model consists of 4 tables:

Purchase Table:

DepartmentNo

VendorNo

Quantity

Date

Negotiation Table:

NegotiationID

VendorNo

DepartmentNo

InitialPrice

FinalPrice

StartDate

EndDate

Department Table:

DepartmentNo

DepartmentName

Vendor Table:

VendorNo

VendorName

Negotiations do not necessarily have a DepartmentNo, depending wheter the negotiation is department-specific or not. This means negotiations without a specific department applies to all puchases for that specific vendor except if there are negotiations with specific departments registered.

What is the best solution to create a lookup column in the purchase table finding the NegotiationID?

Prevously i have tried the following:

NegotiationID =

VAR vendorNo = Purchases[VendorNo]
VAR departmentNo = Purchases[DepartmentNo]
VAR purchaseDate = Purchases[Date]

RETURN
IF(
CALCULATE(SELECTEDVALUE('Negotiations'[NegotiationID])),
FILTER(
ALL('Negotiations'),
vendorNo = 'Negotiations'[VendorNo] &&
departmentNo = 'Negotiations'[DepartmentNo] &&
purchaseDate > 'Negotiations'[StartDate] &&
purchaseDate <= 'Negotiations'[EndDate]
)
)
= BLANK(),
CALCULATE(SELECTEDVALUE('Negotiations'[NegotiationID])),
FILTER(
ALL('Negotiations'),
vendorNo = 'Negotiations'[VendorNo] &&
purchaseDate > 'Negotiations'[StartDate] &&
purchaseDate <= 'Negotiations'[EndDate]
)
),
CALCULATE(SELECTEDVALUE('Negotiations'[NegotiationID])),
FILTER(
ALL('Negotiations'),
vendorNo = 'Negotiations'[VendorNo] &&
departmentNo = 'Negotiations'[DepartmentNo] &&
purchaseDate > 'Negotiations'[StartDate] &&
purchaseDate <= 'Negotiations'[EndDate]
)
)
)

Currently the formula returns blanks

Small note: I have secured that negotiations Start and End date cannot overlap eachother ensuring only 1 negotiation is valid for a period.

Please find attached dummy model here: WeTransfer - Dummy model 

I am very interested in hearing your thoughts and ideas.

Best regards

Troels

[TSchmidt · Accepted solution]

I think i might have found the final solution.

@ERD you are right that i needed to tinker with the filters. A lot of columns to keep track off and i obvously missed this one.

For all the negotiations where there isn't a department i have added Negotiations[DepartmentNo] = BLANK().

The formula now succesfully filters correctly on those negotiations as well. It now looks something like this:

Negotiation ID = 
//any suggestions on how to return the correct negotiationID?
    VAR vendorNo = Purchases[VendorNo]
    VAR departmentNo = Purchases[DepartmentNo]
    VAR purchaseDate = Purchases[Date]
RETURN
    IF(
        CALCULATE(
            MAX(Negotiations[NegotiationID]),
            FILTER(
                ALL(Negotiations),
                    vendorNo = Negotiations[VendorNo] &&
                    departmentNo = Negotiations[DepartmentNo] &&
                    purchaseDate >= Negotiations[Start Date] &&
                    purchaseDate < Negotiations[End Date]
            )
        )
    = BLANK(),
        CALCULATE(
            MAX(Negotiations[NegotiationID]),
            FILTER(
                ALL(Negotiations),
                    vendorNo = Negotiations[VendorNo] &&
                    purchaseDate >= Negotiations[Start Date] &&
                    purchaseDate < Negotiations[End Date] &&
                    Negotiations[DepartmentNo] = BLANK()
            )
        ),
        CALCULATE(
            MAX(Negotiations[NegotiationID]),
            FILTER(
                ALL(Negotiations),
                    vendorNo = Negotiations[VendorNo] &&
                    departmentNo = Negotiations[DepartmentNo] &&
                    purchaseDate >= Negotiations[Start Date] &&
                    purchaseDate < Negotiations[End Date]
            )
        )
    )

Appreciate your time and effort @ERD and @PawarNovil 

Best regards

Troels

[TSchmidt]

Hello @ERD and @PawarNovil 

Thank you for your responses.

@ERD  I have tried using MAX instead of SELECTEDVALUE and i do get much more values in the column now. I have initially used SELECTEDVALUE to ensure it only returns values if it has correctly filtered down the negotiations to a single value. With the MAX value i am not sure the table is filtered to only one value. It could potentially be multiple values and how does it then know which is the correct one?

Using the MAX formula i can give you an example of an error:

Vendor 30309 is correctly given the right NegotiationID i many instances. But if we look at date 27-01-2023 for all departments except 30369045 we can see it returns ID 199.

ID 199 should only be applied to departments 30369045 for the given period:

If my logic is correct the ID should instead have been 108.

I sincerly do not see why it misses the filter on department number in this case?

Best regards

Troels

[ERD]

@TSchmidt , you need to carefully check your data and then you'll see 'why'. 

According to your file and for the vendor in your example:

If we look at the condition separately, you'll get empty result for the first row:

test = 
VAR vendorNo = Purchases[VendorNo]
VAR departmentNo = Purchases[DepartmentNo]
VAR purchaseDate = Purchases[Date]
RETURN
        CALCULATE(
            MAX(Negotiations[NegotiationID]),
            FILTER(
                ALL(Negotiations),
                    vendorNo = Negotiations[VendorNo] &&
                    departmentNo = Negotiations[DepartmentNo] &&
                    purchaseDate > 'Negotiations'[Start Date] &&
                    purchaseDate <= 'Negotiations'[End Date]
            )
        )

Then we go to Negotiations, filter the table to the current vendor and there we go: the only department available is 30369045:

[ERD]

@TSchmidt ,

In your file blanks are returned when the vendor is not found. Also there was incorrect reference for dates, so it should work:

Negotiation ID = 
    VAR vendorNo = Purchases[VendorNo]
    VAR departmentNo = Purchases[DepartmentNo]
    VAR purchaseDate = Purchases[Date]
RETURN
    IF(
        CALCULATE(
            MAX(Negotiations[NegotiationID]),
            FILTER(
                ALL(Negotiations),
                    vendorNo = Negotiations[VendorNo] &&
                    departmentNo = Negotiations[DepartmentNo] &&
                    purchaseDate > 'Negotiations'[Start Date] &&
                    purchaseDate <= 'Negotiations'[End Date]
            )
        )
    = BLANK(),
        CALCULATE(
            MAX(Negotiations[NegotiationID]),
            FILTER(
                ALL(Negotiations),
                    vendorNo = Negotiations[VendorNo] &&
                    purchaseDate > 'Negotiations'[Start Date] &&
                    purchaseDate <= 'Negotiations'[End Date]
            )
        ),
        CALCULATE(
            MAX(Negotiations[NegotiationID]),
            FILTER(
                ALL(Negotiations),
                    vendorNo = Negotiations[VendorNo] &&
                    departmentNo = Negotiations[DepartmentNo] &&
                    purchaseDate > 'Negotiations'[Start Date] &&
                    purchaseDate <= 'Negotiations'[End Date]
            )
        )
    )

[PawarNovil]

Hi @ERD ,

@TSchmidt  already tried above measure and getting wrong answer as he mentioned in the post. 

regards,

Novil

[PawarNovil]

Hi @TSchmidt 

Could you please provide sample dummy data and desired results for better clarification of the above scenario.

Regards,

Novil

[TSchmidt]

Hello @PawarNovil 

Thank you for the quick response.

I have added a pbix dummy model for you to have a look at. The calculated column is located in the Purchases table trying to find each specific related negotiation.

Looking forward hearing from you

Best regards

Troels

[PawarNovil]

Hi @TSchmidt ,

Thank you for the adding PBIX, but I am not able to track down for which vendor and department you are getting wrong NegotiationID or blank. IF you point out it would be very helpful.

regards,

Novil