DAX to Lookup Multiple Fields in Other Table and Return Match

[ChelseaCL]

Hi guys,

Apologies if this is answered elsewhere- I'm continuing to research but keep finding solutions that don't quite work, and I'm not yet experienced enough to figure out how and why they're not working.  

Basically, I have a record of transactions: people accessing software and the date on which they accessed it.  On a separate table, I have "contracts."  This table includes the company name, software name, and the start and end date of the product's contract.  I need to be able to match these individual transactions to which contract they belong to so I can do other transformations.  Basically, I'm looking to create a custom column in the transaction table that gives the index number of the contract.  This is basically to account for the fact that the transaction records have no link to the contracts. x.x

In order to find out which contract the transaction belongs to, I need to look at the transaction's company name, software name, and the date of the transaction.  I want to look up the contracts table and return the index number of the contract that has a matching company name, software name, and... fits within the contract period.  That's been tricky.

So for example, John Smith from Cool Company used Software A on 12/4/2022.  I'd like to lookup the contracts, see which contract matches "Cool Company," Software A, and the right start and end date (say it was the contract from 3/1/2022-3/1/2023), and return that index number.  

Thanks!

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file and show the expected result.

[DataZoe · Accepted solution]

@ChelseaCL 

You can do it as a calculated column on the Transations table, or as a measure (with a couple variations). A measure should be easier on the model (less space, faster) but if you want to build the relationship between the tables based on that index information then you will have to go with calc column. You could probably do something similar in Power Query too. I think only Measure Variant 2 will work if there are overlapping contracts for same company/software.

Calc column on Transations:

Contract Index =

VAR _startdate =

    LOOKUPVALUE (

        Contracts[Start Date],

        Contracts[Company], Transactions[Company],

        Contracts[Software], Transactions[Software]

    )

VAR _enddate =

    LOOKUPVALUE (

        Contracts[End Date],

        Contracts[Company], Transactions[Company],

        Contracts[Software], Transactions[Software]

    )

VAR _indates = [Accessed] >= _startdate

    && [Accessed] <= _enddate

VAR _indexnumber =

    LOOKUPVALUE (

        Contracts[Index Number],

        Contracts[Company], Transactions[Company],

        Contracts[Software], Transactions[Software],

        Contracts[Start Date], _startdate,

        Contracts[End Date], _enddate

    )

RETURN

    IF ( _indates, _indexnumber, BLANK () )

Measure Variation 1:

Contract Index Measure =

VAR _company =

    SELECTEDVALUE ( Transactions[Company] )

VAR _software =

    SELECTEDVALUE ( Transactions[Software] )

VAR _accessed =

    SELECTEDVALUE ( Transactions[Accessed] )

VAR _startdate =

    LOOKUPVALUE (

        Contracts[Start Date],

        Contracts[Company], _company,

        Contracts[Software], _software

    )

VAR _enddate =

    LOOKUPVALUE (

        Contracts[End Date],

        Contracts[Company], _company,

        Contracts[Software], _software

    )

VAR _indates = _accessed >= _startdate

    && _accessed <= _enddate

VAR _indexnumber =

    LOOKUPVALUE (

        Contracts[Index Number],

        Contracts[Company], _company,

        Contracts[Software], _software,

        Contracts[Start Date], _startdate,

        Contracts[End Date], _enddate

    )

RETURN

    IF (

        NOT ( ISBLANK ( _company ) )

            && NOT ( ISBLANK ( _software ) ) && NOT ( ISBLANK ( _accessed ) ),

        IF ( _indates, _indexnumber, "No contract" ),

        BLANK ()

    )

Mearsure Variation 2:

Contract Index Measure TREATAS =

VAR _accessed =

    SELECTEDVALUE ( Transactions[Accessed] )

VAR _index =

    IF (

        ISBLANK ( _accessed ),

        BLANK (),

        CALCULATE (

            IF (

                _accessed >= SELECTEDVALUE ( Contracts[Start Date] )

                    && _accessed <= SELECTEDVALUE ( Contracts[End Date] ),

                SELECTEDVALUE ( Contracts[Index Number] ),

                "No contract"

            ),

            TREATAS ( VALUES ( Transactions[Company] ), Contracts[Company] ),

            TREATAS ( VALUES ( Transactions[Software] ), Contracts[Software] )

        )

    )

RETURN

    _index

[ChelseaCL]

Thanks!  I think I'll need to make a new post in the m query section to refine this (sorry, didn't realize it was different,) but this got me started in the right direction.

[ChelseaCL]

Apologies, I apparently did not understand the difference between a "calculated" column and a "computed" column.  I was trying to put this in as a custom column in the transform screen and eventually figured out why it wasn't working.  I think it would be best to have this as a Power Query computed column, though?  Both because I need this index value present for other tables to use and because I think the data is better set up for it.  Currently trying to figure out how to convert it to Power Query.

[ChelseaCL]

This is what I've got so far.  Still trying to parse the M rules.

_startdate =
LOOKUPVALUE (
'Assets Grouped'[Usage_Start_Date__c],
'Assets Grouped'[Account.Name], 'Transactions by Contract Year'[CompanyName],
'Assets Grouped'[ProductName], 'Transactions by Contract Year'[ProductName],
)
_enddate =
LOOKUPVALUE (
'Assets Grouped'[UsageEndDate],
'Assets Grouped'[Account.Name], 'Transactions by Contract Year'[CompanyName],
'Assets Grouped'[ProductName], 'Transactions by Contract Year'[ProductName],
)
_indates = [Accessed] >= _startdate
&& [Accessed] <= _enddate,
_indexnumber =
LOOKUPVALUE (
'Assets Grouped'[Index],
'Assets Grouped'[Account.Name], 'Transactions by Contract Year'[CompanyName],
'Assets Grouped'[ProductName], 'Transactions by Contract Year'[ProductName],
'Assets Grouped'[Usage_Start_Date__c], _startdate,
'Assets Grouped'[UsageEndDate], _enddate,
)
RETURN
IF ( _indates, _indexnumber, BLANK () )

[ChelseaCL]

Thanks very much!  I'm trying this out and will let you know how it goes.  I think I do need the calculated column.