Forum Discussion

Applicable88's avatar
Applicable88
Icon for Impactful Individual rankImpactful Individual
3 years ago
Solved

Correct use of Except function

Hello, 

I have two sample tables here:

TableA:

MaterialStatus
123456V
123457V
123458V
123459V
123460V
123461V
123462V
123463V
123464V

 

TableB:

MaterialStatus
123462A
123463A
123464A
8888A
8889A
8890A

 

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:

MaterialStatus
123456V
123457V
123458V
123459V
123460V
123461V

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's avatar
    Jihwan_Kim
    3 years ago

    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's avatar
    Applicable88
    3 years ago

    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.

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      Applicable88
      Icon for Impactful Individual rankImpactful Individual
      3 years ago

      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.