cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
pmacfarlane Regular Visitor
Regular Visitor

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 available.

 

IDPolicy or QuoteQuote DatePolicy DateNew Date Column
1Quote01/01/2019null01/01/2019
1Policynull02/01/201901/01/2019
2Policynull03/01/201903/01/2019
3Policynull04/01/201904/01/2019
4Policynull05/01/201904/01/2019
4Quote04/01/2019null04/01/2019

 

From the above table you can see the new column I'm trying to create.  I want to show the quote date for an ID which will take the quote date if there is a quaote, but when there is no quote and only a policy, i will take the policy date as the quote date.

 

 

I hope this makes sense, and thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
Nathaniel_C Super Contributor
Super Contributor

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

Hi @pmacfarlane 

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 

pd.PNG

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

View solution in original post

4 REPLIES 4
Nathaniel_C Super Contributor
Super Contributor

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

Hi @pmacfarlane ,

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

pmacfarlane Regular Visitor
Regular Visitor

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

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?

Nathaniel_C Super Contributor
Super Contributor

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

Hi @pmacfarlane ,

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 Super Contributor
Super Contributor

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

Hi @pmacfarlane 

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 

pd.PNG

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

View solution in original post

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 140 members 1,711 guests
Please welcome our newest community members: