Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Lookup? IF?

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

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

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

     

2 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    6 years ago

    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's avatar
    edhans
    Community Champion
    6 years ago

    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.