Forum Discussion
Helper INeed help on a DAX Formula
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 |
This was a little trickier in DAX than I expected, but here is one way to do it.
Make a Calculated Table with this expression:Filtered = FILTER(Ranks, Ranks[Rank]=CALCULATE(CALCULATE(MAX(Ranks[Rank]), All(Ranks), VALUES(Ranks[Number]), Ranks[Rank] <=2)))If you didn't need a separate DAX table, you could use the same Filter() expression in a Calculate() if needed. Note that the nested Calculate()s were necessary to get context transition to kick in.If this works, please mark it as solution. Kudos are appreciated too.Regards,Pat
5 Replies
- mahoneypat
Microsoft Employee
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
- mahoneypat
This was a little trickier in DAX than I expected, but here is one way to do it.
Make a Calculated Table with this expression:Filtered = FILTER(Ranks, Ranks[Rank]=CALCULATE(CALCULATE(MAX(Ranks[Rank]), All(Ranks), VALUES(Ranks[Number]), Ranks[Rank] <=2)))If you didn't need a separate DAX table, you could use the same Filter() expression in a Calculate() if needed. Note that the nested Calculate()s were necessary to get context transition to kick in.If this works, please mark it as solution. Kudos are appreciated too.Regards,Pat
Yucymahoneypat Thank you
camargos88Community Champion
Yucy ,
In addition to mahoneypat answers, you can create a calculated column on the same table:
Filter =VAR _qt = CALCULATE(COUNTROWS('Table'); ALLEXCEPT('Table'; 'Table'[Number]))RETURN IF(_qt = 1; 1; IF(_qt > 1 && 'Table'[Rank] = 2; 1; 0))Ricardo- Yucy
it is not working for me, not sure why