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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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