Solved: Validation with a Ref Col and implementing ORDERBY - Page 2

aijac1314 · 11-05-2023 06:45 PM

Hi, doing some research of sorting a dropdown list and knoing that we may use the ORDERBY() in VALID_IF.

I am facing a problem here, I do have a column, which is a ref col, and initially I do implemented a VALID_IF to checking the validity of the data that input from user. And now I do need to sort the column value with date, how can I do so?

EG:

VALID_IF: [_this]<>100 <---column validation.

ORDERBY(TABLE[COL],[DATE],true)

how can I insert both of this in VALID_IF?

Your help is appreciated, thank you.

Suvrutt_Gurjar

@aijac1314 wrote:

EG:

VALID_IF: [_this]<>100 <---column validation.

ORDERBY(TABLE[COL],[DATE],true)

It sounds that you are essentially looking for ordering the reference column by date, however you do not wish the user to enter certain values of the reference column. In that case one way will be to omit those invalid values from the choice presented to the user so that you do not have to additionally check that validity.

So your valid_if expression could be something like

ORDERBY(SELECT(TABLE[COL], [COL]<>100), [DATE], TRUE)

You could also create a slice called say "Allowed_Values" on the table with a slice filter expression something like [COL]<>100

Then your valid_if expression can be

ORDERBY(Allowed_Values[COL], [DATE], TRUE)

If there are just one or two values that you do not wish to include, then an easier expression could be something like

ORDERBY(TABLE[COL] -LIST("100", "200"),[DATE],true)

This will ensure that the dropdown value options omit the value of 100 or whatever values you want to omit and present only valid values to the user.

View solution in original post