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

[Anonymous]

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!! 

[Anonymous · Accepted solution]

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

[Anonymous]

Hi,

@Momie2005 , @tamerj1 

I tried, but receives this message;

What is wrong?

[tamerj1]

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" } )
    )
)

[Momie2005]

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

[tamerj1]

Hi @Anonymous 

something like

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