topic A function that displays all other values than some specified in a column! Which function? in DAX Commands and Tips

A function that displays all other values than some specified in a column! Which function?

Anonymous — Fri, 20 May 2022 07:48:39 GMT

Hi,

I have a column with following values;

I want to display all possible values other than A, B, C, D or Z.

I need a function that returns any value other than A, B, C, D or Z as data will continually be added and I want the function to have the opportunity to catch and grab data (0, 1, S and even blank).

What function should I use? Filter or allexcept? I'm unsure.....

Many thanks!!

Re: A function that displays all other values than some specified in a column! Which function?

tamerj1 — Fri, 20 May 2022 08:26:05 GMT

Hi Anonymous

something like

FILTER ( TableName, NOT ( TableName[ColumnName] IN { "A", "B", "C", "D", "Z" } ) )

Re: A function that displays all other values than some specified in a column! Which function?

Momie2005 — Fri, 20 May 2022 08:27:32 GMT

try this one: FILTER ( your_table , NOT ( your_table [colomn]IN { "q", "b", "d", "s", "Z" } ) )

Re: A function that displays all other values than some specified in a column! Which function?

Anonymous — Fri, 20 May 2022 09:04:32 GMT

Hi,

Momie2005 , tamerj1

I tried, but receives this message;

What is wrong?

Re: A function that displays all other values than some specified in a column! Which function?

tamerj1 — Fri, 20 May 2022 09:12:35 GMT

Hi Anonymous
You can create a new tabel with this code or otherwise use to filter data. Example:

Filtered Qty = CALCULATE ( SUM ( 'Stock Item Master Table'[Qty] ), FILTER ( 'Stock Item Master Table', NOT ( 'Stock Item Master Table'[Maintenance Contract Code] IN { "A", "B", "C", "D", "Z" } ) ) )

Re: A function that displays all other values than some specified in a column! Which function?

Anonymous — Tue, 24 May 2022 06:20:03 GMT

Hi Anonymous ,

Since you also want to capture blank values, I use 'others' in the results returned by the formula to differentiate the results.

Please new a column.

Column2 = IF( NOT 'Table'[Column1] IN {"A","B","C","D","Z"},'Table'[Column1],"others")

This way blank values can also be distinguished.

Attached PBIX file for reference.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data