Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

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

I have a table called Address as shown below

EalTim_0-1639306080645.png

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:

EalTim_1-1639306432477.png

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?

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

EalTim_0-1639387085632.png

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

see screenshot below

EalTim_1-1639387182225.png

Next, I removed other columns and left only Custom column

EalTim_2-1639387218599.png

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

EalTim_3-1639387247824.png

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

EalTim_4-1639387282425.png

However, the Adanvce editior code is shown below

EalTim_5-1639387435391.png

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"

 

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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

EalTim_0-1639387085632.png

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

see screenshot below

EalTim_1-1639387182225.png

Next, I removed other columns and left only Custom column

EalTim_2-1639387218599.png

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

EalTim_3-1639387247824.png

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

EalTim_4-1639387282425.png

However, the Adanvce editior code is shown below

EalTim_5-1639387435391.png

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

@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

smpa01_0-1639319374859.png

 

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

 

 

smpa01_2-1639320355159.png

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
bcdobbs
Community Champion
Community Champion

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.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.