Summarise table to show last record using a calculated column

[Anonymous]

Hello,

I have quite a simple problem but I can't figure out a solution as the ones I've seen doing something similar revolve around using Power Query to group the table and then merge with original. However, in my situation I have a calculated column giving a true / false result for a series of IDs. It is possible for an ID to be flagged as false many times before becoming true (as seen in the table - ID 27392).

I would like to create a table where only the last record is used in the table per ID. Currently my output is the following:

Count of true = 61 (correct number)

Count of false = 280 (incorrect due to inclusion of records flagged false when last record was true)

Total unique IDs = 294

Picture of the table 

Sorry if I've explained it poorly, I'm still learning.

Cheers

[amitchandak]

@Anonymous , last record basis not clear in your screenshot

[Pragati11]

Hi @Anonymous ,

When you are moving your True/False column to the table visual, use LAST summarised option on this column to display. It will pick-up the last True/False for your ID.

Thanks,

Pragati

[Anonymous]

Hi again,

Thanks for the quick response. I attached the date field corresponding to the true/false as seen in my screenshot link below. You can see that the all 'true' values are simply flags for when there is a date. I have then selected 'latest' on the visual summary, however, it is now ommitting all false records as they do not technically have a date.

Any ideas?

https://imgur.com/a/RvsVgM0

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ，

Are  you trying to get latest value of true or false for childID and lastest date for it?

If so, you can use the following summarize table:

Table2 = 
SUMMARIZE (
    'Table',
    'Table'[ChildID],
    "LAOfferCheck", LASTNONBLANK ( 'Table'[LAOfferCheck], 1 ),
    "LA_Offered_Date", LASTNONBLANK ( 'Table'[LA_Offered_Date], 1 )
)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai