Lookup? IF?

[Anonymous]

Hi All,

i need help to find a right formula.

I do have 3 colum: Date ( dates ) - Origin ( Text ) - Destination ( Text ). The goal here is to find old and new lanes. 

i am trying to look at ( Origin and Destination column together ) and if these value ( together )  are in column Date ( 2018  ) then is an old lane other wise ( if in 2019 )   is a new lane....

i hope this above is clear.

thanks 

Diego

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

We can create a measure as below.

Measure = 
VAR countr =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( 'Table', YEAR ( 'Table'[date] ) = 2018 )
    )
RETURN
    IF ( ISBLANK ( countr ), "new lane", "old lane" )

Or we can crreate a calculated column to work on it.

Column = 
VAR countr =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[origin], 'Table'[destination] ),
            YEAR ( 'Table'[date] ) = 2018
        )
    )
RETURN
    IF ( countr = BLANK (), "new lane", "old lane" )

[edhans]

Can you provide a sample of your data and the expected result? Hard to write a formula to help when you are trying to compare two text columns to a date column with no context for what the text actually is.