Forum Discussion

fabiomanniti's avatar
fabiomanniti
Helper III
4 years ago
Solved

Lookup a value in same table different column

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_IDOld_IDIssue_dateexpiration_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

 

 

 

Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    4 years ago

    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

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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())

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    4 years ago

    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