Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@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]
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
45 | |
25 | |
19 | |
16 | |
8 |
User | Count |
---|---|
61 | |
50 | |
45 | |
20 | |
17 |