Unique values after filtering

[Sven_Arndt]

Hi All

I am trying to remove certain values and then whatever left after that, only return the unique values in the data. I have managed to get the filter correct using:

AND(IN([Status],LIST(Authorised,Draft)),isnotblank([Person]))

but then get stuck on the unique portion of whats left. So it currently returns 3 authorised with the same persons name, I only need one.

Any ideas?

[Steve]

Where are you trying to do this? In a slice?

[Sven_Arndt]

That is correct

[Steve]

Use this as your slice row filter condition:

([_THISROW] = ANY(ORDERBY(FILTER("table", ...), [_ROWNUMBER])))

Replace table with the name of the table the slice is for, and replace ... with the expression you’d been using previously.

[Sven_Arndt]

Hi @Steve

this does not function as the Any returns only one random value from the list from what I can see. I need all the values returned, minus the duplicates naturally

[Steve]

Did you try it?

[Sven_Arndt]

Yes I put it in and got only one result, changed according to what I set my key as, but still one result

[Steve]

Yeah, I rushed my previous response; it was bad. Try this instead:

(
  [_THISROW]
  = ANY(
    ORDERBY(
      FILTER(
        "table",
        AND(
          ISNOTBLANK([Person]),
          ([Person] = [_THISROW].[Person]),
          IN([Status], LIST("Authorised", "Draft"))
        )
      ),
      [_ROWNUMBER]
    )
  )
)

Replace table with the name of the table the slice is based on.