Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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?
Solved! Go to 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"
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"
@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] ) )
)
)
)
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
68 | |
53 | |
39 | |
33 |
User | Count |
---|---|
70 | |
63 | |
57 | |
49 | |
46 |