Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Create a column that takes a value from another row when a criteria is met

Hi,   I have a dataset that contains a unique identifier that is on multiple rows.  Each row has 2 possible dates and ab indicator.  I would liek to create a column that returns one date from those...
Need Help
  • Nathaniel_C's avatar
    Nathaniel_C
    6 years ago

    Hi Anonymous 

    This checks for a quote date, and if there is not one it uses the policy date and if there is one, then it uses the quote for all rows that have that policy ID. Here is my PBIX POLICY DATE 

    Matches your column.
    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
    Nathaniel

    NewQuoteDate =
IF (
    ISBLANK (
        CALCULATE ( MIN ( PolDate[Quote Date] ), ALLEXCEPT ( PolDate, PolDate[ID] ) )
    ),
    PolDate[Policy Date],
    CALCULATE ( MIN ( PolDate[Quote Date] ), ALLEXCEPT ( PolDate, PolDate[ID] ) )
)
Nathaniel_C's avatar
Nathaniel_C
Icon for Community Champion rankCommunity Champion
6 years ago

Hi Anonymous ,

In Power Query add a conditional column. Use If (table[Quote Date] <> "NULL", table[Quote Date], table[Policy Date])
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thanks very much for the quick reply.  Is it possibel to only use the policy date when there is no associated quote? Like the instances of ID's 2 & 3?