Get opposite relation

[ezequiel]

Hi,

I have 2 tables. The first contains names from a company and dates (first days of weeks) and the second contains all the employees from that company. Example:

Table 1

Name      Date

------       ------

John         8/12/2019

Peter        8/12/2019

Anna        8/12/2019

Anna        8/5/2019

Table 2

Name

-------

John

Peter

Anna

Joyce

What I need to to get those names that don't appear depending on the date selection. Example: If I filter by 8/12/2019, I should get 'Joyce'. If I filter by 8/5/2019, I should get 'Joyce','Peter' and 'John'. 

Can you guys please help me on how to achieve that?

Thanks

Ezequiel

[Anonymous]

You can use the except function:

Measure = 
CONCATENATEX(
    EXCEPT(Name2,SELECTCOLUMNS(NameDate,"Name", NameDate[Name])),Name2[Name],UNICHAR(10))

[ezequiel]

@Anonymousthis looks like the resolution. I'm new to powerBi, so I must ask you where this measure should be created? within the first or the second table?

Thanks!

[Anonymous · Accepted solution]

It's a measure, so it really doesnt matter which table you "create" it in. So  you just want to go to Modeling-New Measure.