DAX Filter function unapplicable

[Anonymous]

Hi all,

I have a visual that is filtered to display only values from categories C, H and I.

It is operated by a switch that selects a measure to apply to the visual based on a slicer.

Anyway, for one measure, I have to apply an additional filter, the category "I". In the sense that I doesn't get displayed anymore

I tried it using this formula:

€ Net Value = CALCULATE(SUM(Distincttable[Euro]), FILTER(ALLEXCEPT('UNION', 'UNION'[DocCa] = I))

However, PowerBI keeps telling me this measure, specifically the I is wrong. 

I don't know why as UNION is linked to Distincttable and also when I added the DocCa Column to Distincttable it didn't work. So yeah, I hope you guys can tell me where I made the mistake?

Also I'm unsure if using ALLEXCEPT would remove the preexisting visual-level-filters, so in the end not only values from C & H are displayed, but from all categories with the exception of I.

[FarhanAhmed]

Try this.

€ Net Value = CALCULATE(SUM(Distincttable[Euro]), FILTER(ALLEXCEPT('UNION', 'UNION'[DocCa]) 'UNION'[DocCa]= "I"))

[PaulDBrown · Accepted solution]

@Anonymous 

The are two errors in the measure:
1) ALLEXCEPT requires a table reference and a column reference. This function removes the filters on the specified table except for the column included). So in your example, the expression would read
ALLEXCEPT(UNION, UNION[DocCa]).

In the following example you can see how it works. The measure reads:

Sales ALLEXCEPT Item =
CALCULATE ( [Sum of Sales], ALLEXCEPT ( Actuals1, Actuals1[Item] ) )

And this is what you get:

 So the expression removes all filters from the table except the filte on "Item". Therefore the total sum is by item only (regardless of Channel or Product Ref. (A bit like grouping totals)

So this is not the function you need to filter out rows with "I"

2)  When specifying a text value as a reference in an expression, you need to wrap it in "". So in this case it would be "I"

If you want to filter out rows with "I", try:

€ Net Value = CALCULATE(SUM(Distincttable[Euro]),
                          FILTER('UNION',
                             'UNION'[DocCa] <> "I"))

[Anonymous]

First off, thank all commentators for the answer however I think yours is the best and most explaining one.

For people who might happen to have the same problem:

It is mandatory that you use "<>" to filter not for I but exclude I.

Thank you very much!

[amitchandak]

@Anonymous , try like

€ Net Value = CALCULATE(SUM(Distincttable[Euro]), FILTER('UNION', 'UNION'[DocCa] = "I"),ALLEXCEPT('UNION', 'UNION'[DocCa]))