Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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 , as always very good explained. Thank you very much, that is the expected outcome. I will read more into usage of TREATAS() -Function.
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 , as always very good explained. Thank you very much, that is the expected outcome. I will read more into usage of TREATAS() -Function.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
9 | |
7 | |
6 | |
6 |
User | Count |
---|---|
29 | |
11 | |
11 | |
10 | |
6 |