Filtering ref column by unique label?

Binkodyl
Participant II

I have a column called [DataID] that references a table called “Data”. I have the label of data set to a column called [String] which has duplicate values in multiple rows, but mean different things. How can I filter the key values showing labels to be unqiue? I only want to display unique values from that column, but other rows have the same [String] value. So instead of that ref column showing five “1” values, I’d like it to only show one. How can I do this?

Solved Solved
0 12 1,267
1 ACCEPTED SOLUTION

Try:

FILTER(
  "Data",
  ISBLANK(
    FILTER(
      "Data",
      AND(
        ([_ROWNUMBER] < [_THISROW-1].[_ROWNUMBER]),
        ([String] = [_THISROW-1].[String])
      )
    )
  )
)

See also:

View solution in original post

12 REPLIES 12

Steve
Participant V

Where would you like this list displayed? In a dropdown? In a table view?

It displays in a dropdown. What I’m looking for is an expression.


DataID is my key, and String is the label. I have a ref to DataID in another table. Is there a valid if constraint that only displays values in the dropdown with the condition that the string column value is unique? So that I only get one 1, and two 2’s?

When the user makes a selection from the dropdown, what value will you want saved in the column? The Ref value, or the label value?

I want to limit the key values represented by the label to be filtered by the label so that Ids will be shown in the dropdown only for unique label values. Will what you suggested accomplish this?

What value will you want stored?

The Key is fine. I have it setup so that when a key is selected, the string field is populated. The only issue is how many key values are visible with duplicate string values.

When there are multiple keys with the same string value which key do you want saved?

Preferably the First key with a given string value.

Try:

FILTER(
  "Data",
  ISBLANK(
    FILTER(
      "Data",
      AND(
        ([_ROWNUMBER] < [_THISROW-1].[_ROWNUMBER]),
        ([String] = [_THISROW-1].[String])
      )
    )
  )
)

See also:

Dear steve,
I have the same case and will save the Ref Value, is it the same expression?

UNIQUE(Data[String]) will give a list of distinct values in the String column of the Data table, as will SELECT(Data[String], TRUE, TRUE) (note two TRUE values there). The SELECT() approach could also be used to further filter the possible values.

See also:


Binkodyl
Participant II

That worked perfectly! Thank you so much Steve!

Top Labels in this Space