measure or column to filter out record from a table

[Anonymous]

Hi,

I am trying to filter out a particular value from a table using DAX, but when I try to use Filter or ALLExcept I am getting errors.

kindly help me with the formula.

Ravi

[v-ljerr-msft]

Hi @Anonymous,

In addition, instead of creating a measure or column, you can try creating a new table in this scenario.

Regards

[Anonymous]

Thanks JJ and v-ljerr-msft , is it possible to have the filter in a formula without creating a new table?

Thanks,

Ravi

[v-ljerr-msft · Accepted solution]

Hi @Anonymous,

Basically, the filtering functions let you manipulate data context to create dynamic calculations.

For example, if you want to count the number of "Associate Id" which is not "2014", then you can just use the formula below to create a measure for it and show the measure on the report.

Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Final_Data'[Associate Id] ),
    FILTER ( 'Final_Data', 'Final_Data'[Associate Id] <> 2014 )
)

Regards

[Anonymous]

Thanks v-ljerr-msft, that was very helpful ! but I am still not clear with some of the filter options 😞

[KHorseman]

I'm trying to figure out how you expect it to work. If you're adding a column to a table, what should that new column look like? Should it copy another column in the same table but remain blank on rows where that first column has a certain value?

[DoubleJ]

Hi

Your formula returns a TABLE with of Type "Final_Data" including all records where "Associate ID" is not 2014. There could be many records fitting that filter. In a column of a row only a single value can be stored.

Could apply this filter already when importing the data?

JJ