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 a table with Staff Name and Status Number as follows:
Date | Staff Name | Status Number |
01/11/2022 | A | 1 |
01/11/2022 | A | 2 |
01/11/2022 | A | 3 |
01/11/2022 | A | 4 |
01/11/2022 | B | 1 |
01/11/2022 | B | 2 |
01/11/2022 | B | 3 |
01/11/2022 | B | 4 |
01/11/2022 | C | 1 |
01/11/2022 | C | 3 |
01/11/2022 | C | 4 |
01/11/2022 | D | 1 |
01/11/2022 | D | 2 |
01/11/2022 | D | 3 |
01/11/2022 | D | 4 |
01/11/2022 | E | 1 |
01/11/2022 | E | 3 |
01/11/2022 | E | 4 |
I need a calculated table from this such that it if a STAFF NAME has STATUS NUMBER = 2, the rows corresponding to that particular staff name should be ignored. In this case, the output for the above table should be:
Date | Staff Name |
01/11/2022 | C |
01/11/2022 | E |
I tried creating a calc table from the main table filtered with STATUS = 2 and then tried an anti join using EXCEPT function but it does not work.
Any help is deeply appreciated. Thanks in advance!
Midhun
Solved! Go to Solution.
Then you can try something like this:
Hi,
Could you try to create a calculated table with the following DAX query:
New calculated table =
CALCULATETABLE (
SUMMARIZECOLUMNS ( Table[Date], Table[Staff name], Table[Staff number] ),
NOT ( Table[Staff number] ) = 2
)
Br
Marius
Hi,
This does not work as the output will have all entries with STATUS NUMBER not equal to 2:
Date | Staff Name | Status Number |
01/11/2022 | A | 1 |
01/11/2022 | A | 3 |
01/11/2022 | A | 4 |
01/11/2022 | B | 1 |
01/11/2022 | B | 3 |
01/11/2022 | B | 4 |
01/11/2022 | C | 1 |
01/11/2022 | C | 3 |
01/11/2022 | C | 4 |
01/11/2022 | D | 1 |
01/11/2022 | D | 3 |
01/11/2022 | D | 4 |
01/11/2022 | E | 1 |
01/11/2022 | E | 3 |
01/11/2022 | E | 4 |
Thanks!
Hi again 🙂
Ok, so what you want is that all staff name that equal to the same name as staff number 2 should be filtered away?
Marius
Then you can try something like this:
Hi Marius,
I get an empty table returned when i use the above.
Thanks!
Midhun
Hi again.
I have created a test .pbix file where this is working as expected, but I cant upload it on this website. Would you like me to send it on email?
Br
Marius
Hello Marius. That would be great!
Can you please send it to midmurali57@gmail.com?
I have now sendt you an email with the example .pbix file that works as expected 🙂
Please let me know how it goes.
Br
Marius
Hi Marius,
If there is a STATUS NUMBER = 2 corresponding to any name, I want my final table to not include that particular name.
I hope you understood now.
Thanks
Midhun
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 |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |