Forum Discussion
Calculated Table from based on values from another table
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...
Then you can try something like this:
Calculated table =VAR __Filter =CALCULATETABLE( VALUES( Staff[StaffName] ), Staff[StaffNumber] = 2 )VAR __Result =FILTER (SUMMARIZECOLUMNS ( Staff[Date], Staff[StaffName] ),NOT ( Staff[StaffName] ) IN __Filter)RETURN __ResultBrMarius
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
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
Then you can try something like this:
Calculated table =VAR __Filter =CALCULATETABLE( VALUES( Staff[StaffName] ), Staff[StaffNumber] = 2 )VAR __Result =FILTER (SUMMARIZECOLUMNS ( Staff[Date], Staff[StaffName] ),NOT ( Staff[StaffName] ) IN __Filter)RETURN __ResultBrMariusHi 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?
BrMarius