Correct use of Except function

[Applicable88]

Hello, 

I have two sample tables here:

TableA:

Material	Status
123456	V
123457	V
123458	V
123459	V
123460	V
123461	V
123462	V
123463	V
123464	V

TableB:

Material	Status
123462	A
123463	A
123464	A
8888	A
8889	A
8890	A

So now I want to filter TableA with TableB. Every Materialnumber which is in both tables, should be filtered out in the new Except Table. 

As you can see following numbers are in both tables:

123462

123463

123464

When I make a new calculated table with:

Except Table =
Except (TableA, TableB)

My expectation was that to return:

Material	Status
123456	V
123457	V
123458	V
123459	V
123460	V
123461	V

The three numbers should be filtered out, but instead I got TableA returned again with all rows. Nothing changed

On the otherhand adding Values seems to work: 

Example Table Test NO.2 = 


EXCEPT(CALCULATETABLE(Values(TableA[Material]), TableA[Status]= "V"), CALCULATETABLE(Values(TableB[Material]), TableB[Status]= "A"))

I got the right filtered Materialnumbers returned but the second column "Status" is missing. 

Material

123456

123457

123458

123459

123460

123461

I hope someone can tell me what is happening here. Why in the first example Except didn't do the expected outcome.

And if Values needs to be used, how to keep all Columns?

Thank you very much in advance .

Best. 

[Jihwan_Kim · Accepted solution]

Hi,

In my opinion, 123462/V from tableA and 123462/A from tableB are different. 

If you can try to compare 123462/V from tableA and 123462/V from tableB, I think EXCEPT function may work as you expect.

But, by using the above sample, please check the below and the attached file.

Example Table Test NO.2 =
CALCULATETABLE (
    TableA,
    TREATAS (
        EXCEPT (
            CALCULATETABLE ( VALUES ( TableA[Material] ), TableA[Status] = "V" ),
            CALCULATETABLE ( VALUES ( TableB[Material] ), TableB[Status] = "A" )
        ),
        TableA[Material]
    )
)

[Jihwan_Kim · Accepted solution]

Hi,

In my opinion, 123462/V from tableA and 123462/A from tableB are different. 

If you can try to compare 123462/V from tableA and 123462/V from tableB, I think EXCEPT function may work as you expect.

But, by using the above sample, please check the below and the attached file.

Example Table Test NO.2 =
CALCULATETABLE (
    TableA,
    TREATAS (
        EXCEPT (
            CALCULATETABLE ( VALUES ( TableA[Material] ), TableA[Status] = "V" ),
            CALCULATETABLE ( VALUES ( TableB[Material] ), TableB[Status] = "A" )
        ),
        TableA[Material]
    )
)

[Applicable88 · Accepted solution]

@Jihwan_Kim , as always very good explained. Thank you very much, that is the expected outcome. I will read more into usage of TREATAS() -Function.