Need help on a DAX Formula

[Yucy]

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

[camargos88]

@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

[mahoneypat · Accepted solution]

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

[Yucy]

@mahoneypat Thank you

[mahoneypat]

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