How to use Summarize to reduce records base on USERID and Index column

[Anonymous]

I have a table called Address as shown below

This table contains userid, index, fullname, Address and AddressStatus.
How can I use summarize function or any function to retrieve userid with Index 1 that addressstatus is current.

See below what I want to achieve:

The first screenshot has 11 records and what i want to achieve screenshot has 3.

Please, is there a way to achieve this with any function?

[Anonymous · Accepted solution]

Thanks @smpa01 @smpa01 and@bcdobbs for you reply. However, the solution offered above didn't work.

I have found the solution to my problem. let me share.

From the Address table above, opened the power query, then applied filter rows to keep only index = 1.
Next, I applied Group By on Userid for all rows -  see screenshot below

Next, I applied custom Column by added index column called Custom as shown below
Custom = Table.AddIndexColumn([Count], "RankUserId",1,1) 

see screenshot below

Next, I removed other columns and left only Custom column

I expanded the custom column, then note, it has been ranked

Then apply filter on RankUserid where RankuserId = 1 and the below screenshot is my final result

However, the Adanvce editior code is shown below

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZBNa8MwDIb/isi5h8ZO93HsCjuMDcbayyg9eLXamtjWkO1A//1sSGAjJdlBBwk9PNK731f1sq4WVakP+jIedupqicsInGkRmJTO3SYxo4+w1poxhOqwGFAxRoWEJ/QD+s7YGUrhBivHbLOCcFFM/8FLvZUjn4nLai3gzEQdgupw9ujfpJANvBpr878TTtE71xenNHymkE65e1zCMYV4nY5K9Na/rFiBS8fW+HN/8oRZjujmDk6MGjSbObgZwQ/3EMlBiIwY53/eGQfbZIumWJVvJ0OWA5YVL+TLYp2D2lr6Bqv8TfDwAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [userid = _t, Index = _t, FullName = _t, Adresses = _t, AddressStatus = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"userid", Int64.Type}, {"Index", Int64.Type}, {"FullName", type text}, {"Adresses", type text}, {"AddressStatus", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Index] = 1)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"userid"}, {{"Count", each _, type table [userid=nullable number, Index=nullable number, FullName=nullable text, Adresses=nullable text, AddressStatus=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "RankUserId",1,1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"userid", "Index", "FullName", "Adresses", "AddressStatus", "RankUserId"}, {"userid", "Index", "FullName", "Adresses", "AddressStatus", "RankUserId"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Custom", each ([RankUserId] = 1))
in
    #"Filtered Rows1"

[smpa01]

@Anonymous  You can write a measure like this

Measure = 
CALCULATE (
    MAX ( user[Address] ),
    FILTER (
        user,
        user[Address Status] = "Current Address"
            && user[Index] = 1            
    )
)

which will give you following

But within the specified coniditions there are multiple instances and I can see theat you only want the first instnce to be returned.

To be able to achieve that the data set need s to have a order column like this

Measure = 
CALCULATE (
    MIN ( user[Address] ),
    CALCULATETABLE (
        user,
        FILTER (
            user,
            user[Index] = 1
                && user[Address Status] = "Current Address"
                && user[Date] = CALCULATE ( MIN ( user[Date] ), ALLEXCEPT ( user, user[userid] ) )
        )
    )
)

[bcdobbs]

You're not really summariseing but just filtering your existing table. If you really need to materialise this as a separate calculated table in DAX you could do:

NewTable = 
   CALCULATETABLE (
      Address,
      Address[Index] = 1,
      Address[AddressStatus] = "Current Address"
)

Equally you could duplicate your original table in Power Query and apply the filters there.