Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Thanks so much for any help. I have a table, I need to use filter dax, but never working for me
Attached is a screenshot, I need to keep the rows highlighted in yellow. Generally speaking, if number is unique, keep the row, if the number in not unique, keep the row of rank at 2
Thanks
Number | Amended Opened Datetime | Resolved | Rank | Case Duration |
CS0075249 | 1/02/2020 8:00 | 1/02/2020 9:48 | 2 | 6488 |
CS0075249 | 1/02/2020 8:00 | 1/10/2020 0:00 | 1 | 194400 |
CS0075250 | 1/02/2020 8:00 | 1/10/2020 0:00 | 1 | 194400 |
CS0075270 | 1/02/2020 8:12 | 1/02/2020 11:46 | 3 | 12836 |
CS0075270 | 1/02/2020 8:12 | 1/03/2020 8:46 | 2 | 34458 |
CS0075270 | 1/02/2020 8:12 | 1/11/2020 0:00 | 1 | 226052 |
CS0077204 | 1/08/2020 10:26 | 1/08/2020 11:20 | 4 | 3236 |
CS0077204 | 1/08/2020 10:26 | 1/09/2020 9:00 | 3 | 27273 |
CS0077204 | 1/08/2020 10:26 | 1/10/2020 8:16 | 2 | 56986 |
CS0077204 | 1/08/2020 10:26 | 1/18/2020 0:01 | 1 | 250417 |
CS0077205 | 1/08/2020 10:25 | 1/08/2020 10:26 | 1 | 71 |
Solved! Go to Solution.
This was a little trickier in DAX than I expected, but here is one way to do it.
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@Yucy ,
In addition to @mahoneypat answers, you can create a calculated column on the same table:
it is not working for me, not sure why
This was a little trickier in DAX than I expected, but here is one way to do it.
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
If you don't need the unhighlighted rows in your model, I would do this in M/Query Editor as below (paste it into a blank query in Advanced Editor). It can also be done in DAX and I may post that next.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZFBDsMgDAS/UnGOFHuxwXDtE3qM8v9vFJLQhkZqkXrwgREj78KyuPuDKCokucnxTJhBoJtlog6kLFYAygQxc+v0U2XaAR2gThIph5Os9IccP2VGB5izhEJ8pTAfhmTfwOZW6EXURlzmS2ogkOItR5Dssh0hKSP0hDOqXu95nFN/k1P7qG11rYyI6Ifk9tqlSOusIdnYZrZXaW6llYRjZ+vFvpJw6JHduj4B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Number = _t, #"Amended Opened Datetime" = _t, Resolved = _t, Rank = _t, #"Case Duration" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", type text}, {"Amended Opened Datetime", type datetime}, {"Resolved", type text}, {"Rank", Int64.Type}, {"Case Duration", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Number"}, {{"Count", each Table.RowCount(_), type number}, {"AllRows", each _, type table [Number=text, Amended Opened Datetime=datetime, Resolved=text, Rank=number, Case Duration=number]}}),
#"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Amended Opened Datetime", "Resolved", "Rank", "Case Duration"}, {"Amended Opened Datetime", "Resolved", "Rank", "Case Duration"}),
#"Added Custom" = Table.AddColumn(#"Expanded AllRows", "Keep", each if [Count]=1 then "Y" else if [Rank]= 2 then "Y" else "N"),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Keep] = "Y")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Keep", "Count"})
in
#"Removed Columns"
If this meets your need, please mark this as the solution.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |