Calculated Table from based on values from another table

[Anonymous]

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

@amitchandak @Greg_Deckler 

[mariussve1]

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

[Anonymous]

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!

[mariussve1]

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

[mariussve1 · Accepted solution]

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 __Result

Br

Marius

[Anonymous]

Hi Marius,

I get an empty table returned when i use the above.

Thanks!

Midhun

[mariussve1]

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

[Anonymous]

Hello Marius. That would be great!

Can you please send it to midmurali57@gmail.com?

[mariussve1]

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

[Anonymous]

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