Need help with power query or DAX formula

[manipepsi]

Hi,

I have 4 columns in a table.

First Column is a unique ID, Second column is the ID (same as first column but has multiple entries),

thrid column is the status and fourth column is date.

I need to take the unique ID, compare and filter the second column. If there are multiple values then I need the status of the latest date and print it in a new fifth column.

UniqueID	ID	STATUS	DATE
123456	123456	ACTIVE	30-Jun-21
344566564	344566564	ACTIVE	30-Jun-21
242423423	242423423	ACTIVE	30-Jun-21
23232	23232	ACTIVE	30-Jun-21
22323232	22323232	ACTIVE	30-Jun-21
23232323	23232323	ACTIVE	30-Jun-21
	123456	INACTIVE	30-Jul-21
	344566564	ACTIVE	31-Jul-21
	242423423	INACTIVE	15-Aug-21
	23232	ACTIVE	15-Aug-21

Can you please help me to achieve the result. Thank you

[v-yiruan-msft · Accepted solution]

Hi @manipepsi ,

Please try to deal with it in Power Query Editor with the below applied codes and check whether you still get the errpr message with "there is no enough memory"... You can find the details in the attachment.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjE1U9JBMBydQzzDXIEMYwNdr9I8XSNDpVidaCVjE6C0mamZCUgGiY1DuZEJEBoDEVAGmY1LuTEQgpRCaVzKwPIQlQgmPjONIS5AMHEoRg4BTz9URTlIirB73RBNFbKPkUwzNNV1LE1HVofuAyQVsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [UniqueID = _t, ID = _t, STATUS = _t, DATE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"UniqueID", Int64.Type}, {"ID", Int64.Type}, {"STATUS", type text}, {"DATE", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Latest date", each List.Max([DATE]), type nullable date}, {"Details", each _, type table [UniqueID=nullable number, ID=nullable number, STATUS=nullable text, DATE=nullable date]}}),
    #"Expanded Details" = Table.ExpandTableColumn(#"Grouped Rows", "Details", {"UniqueID", "STATUS", "DATE"}, {"UniqueID", "STATUS", "DATE"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Details", "Latest Status", each if [DATE]=[Latest date] then [STATUS] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Latest date"})
in
    #"Removed Columns"

The detailed steps as below:

1. Get the latest date group by ID and expand the details

2. Add conditional column to get the status with latest date

3. Remove the latest date column

Best Regards

[manipepsi]

Hi @v-yiruan-msft 

Thanks for your help.

Your solution fullfilled my need.

Thank you!

[manipepsi]

Thanks @VahidDM for the quick reply.

I have still 2 questions:

1) In the "Latest Status" whether it is possible to have the latest status (Inactive, active etc.) only for "Unique ID" ?

Meaning: What I need is the latest status for every Unique ID so that I can filter the latest statuses which I need.

In this solution I cannot able to filter 2 statuses and get the unique IDs of those.

2) Whether it is possible to have "Latest Status" as a column and not as a measure?

Can you please also help with these questions?

Thanks in advance!!

[VahidDM]

Hi @manipepsi 

Yes, It is.

Add a new column and try the below measure:

Latest status = 
VAR _ID = 'Table'[UniqueID]
    
RETURN
    VAR _Maxdate =
        CALCULATE (
            MAX ( 'Table'[DATE] ),
            FILTER ( ALLEXCEPT ( 'Table', 'Table'[ID] ), MAX ( 'Table'[ID] ) )
        )
    RETURN
        CALCULATE (
            FIRSTNONBLANK ( 'Table'[STATUS], "" ),
            FILTER ( ALL ( 'Table' ), 'Table'[ID] = _ID && 'Table'[DATE] = _Maxdate )
        )

The output will be as below:

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos  !!

[manipepsi]

HI @VahidDM 

Thanks for your quick response.

I tried your solution but I got the error "There is no enough memory to complete this operation" .

I have 16GB of RAM but my data has 300K rows.

Whether there is any possibility to have a memory efficient solution?

Thank you

[VahidDM]

Hi @manipepsi 

Please try this measure:

Latest status = 
VAR _ID =
    IF (
        ISBLANK ( MAX ( 'Table'[UniqueID] ) ),
        MAX ( 'Table'[ID] ),
        MAX ( 'Table'[UniqueID] )
    )
RETURN
    VAR _Maxdate =
        CALCULATE (
            MAX ( 'Table'[DATE] ),
            FILTER ( ALLEXCEPT ( 'Table', 'Table'[ID] ), MAX ( 'Table'[ID] ) )
        )
    RETURN
        CALCULATE (
            FIRSTNONBLANK ( 'Table'[STATUS], "" ),
            FILTER ( ALL ( 'Table' ), 'Table'[ID] = _ID && 'Table'[DATE] = _Maxdate )
        )

the output will be as below:

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos  !!