Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |