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/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
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
9 Replies
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