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

Nathaniel_C's avatar
Nathaniel_C
Icon for Community Champion rankCommunity Champion
6 years ago

Hi Anonymous ,

So, looking at your table more closely, the logic is:

Use the quote date for both lines, else use the policy date.

Let me know if that is the case. I will be back on line in a couple of hours.

Nathaniel

  • Nathaniel_C's avatar
    Nathaniel_C
    Icon for Community Champion rankCommunity Champion
    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] ) )
)