Lookup a value in same table different column

[fabiomanniti]

Hello, I have a table with contracts details.

Any contract has an ID and a Issue date.

It might happens when a contract expires the customer wants to renew it so it changes ID.  

For this reason I have a table like this

Current_ID

Old_ID

Issue_date

expiration_date

I would like to check how many contracts expired on September has not been renewd.

In order to get this, I need to know how many "current_id" are not in the column "old_id".

I tried with the LOOKUPVALUE but I get an error

has_been_renewed = LOOKUPVALUE(Contracts[Current_id],Contracts[Old_ID],Contracts[Current_id]))

May somebody help me with this?
Thank you

[v-janeyg-msft · Accepted solution]

Hi, @fabiomanniti 

When the lookupvalue function is used in the measure, the column name can't be used directly in the red line part, but the aggregated value can be used. And you can extend the measure to filter the data for September.

Reference:LOOKUPVALUE function (DAX) - DAX | Microsoft Docs

According to your description, I create a sample. If you want to dynamically select the month, you can create a new date column for filtering. Then create two measures, one for display matching rows, one for count.

Table = CALENDARAUTO()

has_been_renewed 2 = 
CALCULATE (
    LOOKUPVALUE (
        Contracts[Current_ID],
        Contracts[Old_ID], MAX ( Contracts[Current_ID] )
    ),
    Contracts[expiration_date] in DISTINCT('Table'[Date])
)

count_has_been_renewed = 
COUNTX (
    ADDCOLUMNS (
        Contracts,
        "a",
            CALCULATE (
                LOOKUPVALUE (
                    Contracts[Current_ID],
                    Contracts[Old_ID], MAX ( Contracts[Current_ID] )
                ),
                Contracts[expiration_date] IN DISTINCT ( 'Table'[Date] )
            )
    ),
    IF ( NOT ( ISBLANK ( [a] ) ), [a] )
)

PBIX file sample is below.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

[amitchandak]

@fabiomanniti , I think you need a measure for that, either sep is selected as date or we can assume static

Plot this against Current_ID

Measure =
var _date = date(2021,09,30)
var _exp = summarize(filter(Table, eomonth(Table[expiration_date],0) =_date),[Current_ID])
return
if( isblank(countrows(filter(Table , Table[Old] in _exp && Table[expiration_date] >_date))) && not(isblank( countrows(filter(Table , Table[Current_ID] in _exp && Table[expiration_date] <=_date) ))) ,[Current_ID] , blank())