DAX filter column string values being shown in UI

0
votes

I am displaying values from a specific column in a table, say TableP.

This column has some invalid data that I want to filter out.

Using a DAX measure,

Measure = IF ( 'TableP'[columnValue] <> "[email protected]", 'TableP'[columnValue]  )

So with the above code, I am getting an error of

A single value cannot be determined.

So clearly wrong approach. Would appreciate some guidance on best practices :)

Is this the best way to solve this issue

1
powerbidax

1 Answers

1
votes

The best option is to filter the records from your data source in data cleaning step in using Power Query editor. But if it is a requirement and you can not perform that, you can create your measure as below-

Measure  = 
CALCULATE(
    SUM(TableP[your_measure_column]),
    NOT (TableP[columnValue] IN{"[email protected]", "[email protected]"})
)

Here is another option you can try your luck with-

Measure  = 
CALCULATE(
    SUM(TableP[your_measure_column]),
    FILTER(
        TableP,
        NOT(TableP[columnValue] IN{"[email protected]", "[email protected]"})
    )
)

And.... another option :)

Measure  = 
CALCULATE(
    SUM(TableP[your_measure_column]),
    NOT CONTAINSROW(
        {"[email protected]", "[email protected]"},
        TableP[columnValue]
    )
)