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

     

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.