Limiting table visual to records in a single source

[crispybits77]

I have a table visual that draws from two different source data tables. For simplicity with an example the source tables look like this:

Source1:	Emp ID	Name	Source2:	EmpID	Name of Pet
	1	Steve		1	Spot
	2	Bob		2	Fido
	3	Jane		2	Max
	4	Laura		5	Boots
	5	Sam		5	Scruff

I want the table to show all the records with a pet from the second source table, but also pull in the name from the first source table. What I actually get is this:

Emp ID	Name	Name of Pet
1	Steve	Spot
2	Bob	Fido
2	Bob	Max
3	Jane
4	Laura
5	Sam	Boots
5	Sam	Scruff

I've tried putting a filter on the visual to not show records where Name of Pet is blank or empty, but that appears to be filtering only based on the second source table, it doesn't remove the records that don't exist in that table at all from the visual.

Probably missing something obvious but is there any way to not show Jane and Laura in this case, and only show the other 3?

[v-jianpeng-msft]

Your solutions is great @hackcrr   
Hi, @crispybits77 

 Have you solved your problem? If so, can you mark the correct answer as a standard answer to help other members find it faster. Thank you very much for your kind cooperation!

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

[crispybits77]

Thanks @hackcrr 

Is there much performance penalty for this? In the real world scenario my tables both have 10,000s of rows.

[hackcrr · Accepted solution]

Hi, @crispybits77 

Thank you very much for your reply. I think you can try him. Additionally, you can create a relationship for two tables and put one of the ID columns into table visual to get a similar effect:

I prefer to build relationships, which will optimize performance.

Best Regards,

hackcrr

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[hackcrr]

Hi, @crispybits77 

Based on your description, you could create a new Calculated Table or Calculated Column, use DAX to merge the two tables, and make sure to select only those EmpIDs that exist in both tables.

Here are the two source tables I used:

I created a calculation table using the following DAX expression:

Merged Table = 
FILTER(  
    NATURALLEFTOUTERJOIN('Table', 'Table1'),  
    NOT ISBLANK('Table'[Name of Pet])  
)

Use this new Merged Table in the table visual object:

Best Regards,

hackcrr

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.