Forum Discussion

ezequiel's avatar
ezequiel
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago
Solved

Get opposite relation

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

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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. 

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    You can use the except function:

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

    • ezequiel's avatar
      ezequiel
      Icon for Microsoft Employee rankMicrosoft Employee
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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.