Mixing and matching my data! (probably not possible)

[jgaard]

I have a data source that I do not control. And that I cannot request changes to.
I use it for 2 list visuals.

List1 is based on data that I can link to teamname and employee initials.

List2 is based on data that I can link to employee initials only.

I would like to add a slicer based on the team name. Can I in anyway make List2 follow the choise in the slicer, based on the employe initials known to be part of that team? 

[Bibiano_Geraldo]

Hi @jgaard ,

You need a table that connects team names to employee initials. This table can be created manually if it's static or as a query in Power BI if it's derived from existing data. The structure should look like this:

Team Name Employee Initials

Team A	AB
Team A	CD
Team B	EF
Team B	GH

You can name this table as TeamEmployee.

2. Establish Relationships

• List1: Create a relationship between List1 and TeamEmployee based on Employee Initials.
• List2: Create a relationship between List2 and TeamEmployee based on Employee Initials.
• Add a slicer to your report based on the Team Name column from the TeamEmployee table.

[danextian]

hi @jgaard 
The question now is how are you connected to those data sources? If through import, you should be able to create  a relationship between these two tables. If through a live connection and depending on the workspace/tenant settings which is most likely not within your control, you might be able to change the connection to direct query which will add a local model. To  do this, you would want to add another data source which  will prompt you to change the connection to direct query if allowed.

In the screenshot below, my current mode is live connection but since I'm adding another data source im now being prompted to add a local copy and convert the connection to Direct Query. With Direct Query, I can modify the relationship and add calculated columns as well to some extent ( not all operations are supported with Direct Query). Calculated tables is also possible.

[DataNinja777]

Hi  @jgaard ,

Initials are not suitable as unique identifier keys because they are likely to result in duplicates. 😅 I recommend using employee IDs instead for a more robust and reliable data model.

Best regards,

[jgaard]

In this case a uniqe employee ID is also avaliable. So I will probably be using that for the linking. But since initials are also used for email, they will be uniqe. And not all will match the name 100%. 😉

[johnt75]

Create a new table like

Employees = SUMMARIZE( List1, List1[Team], List1[Initials] )

and then link that to both of your list tables. you can then use the team column from the new table as a slicer

[jgaard]

That is very neat. 
But I can not create a New table in this setup. The button is grayed out. 

[Anonymous · Accepted solution]

Hi, @jgaard 

Thanks for all replies. According to your description, the New Table function is grayed out, indicating that you are currently using the Live Connection function to connect to the data source, and this connection mode does not support the creation of tables and modification of the relationship between tables, so the effect you want can not be achieved, it is by design.
In this situation, the only solution is to respond to the database administrator, requesting that you want to create a new table and add a relationship to it.

Live Connection:

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[johnt75]

You could create a measure to use as a filter.

List 2 is visible =
IF ( SELECTEDVALUE ( List2[Initials] ) IN VALUES ( List1[Initials] ), 1 )

Add this as a filter to the visual and set it to only show when the value is 1.

[jgaard]

I'll try this but just found out that the 2 tables are linked togther through a 3'rd table. I think it will make it slightly more complicated.