Forum Discussion

crispybits77's avatar
crispybits77
Icon for Helper I rankHelper I
2 years ago
Solved

Limiting table visual to records in a single source

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 IDNameSource2:EmpIDName of Pet
 1Steve 1Spot
 2Bob 2Fido
 3Jane 2Max
 4Laura 5Boots
 5Sam 5Scruff

 

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 IDNameName of Pet
1SteveSpot
2BobFido
2BobMax
3Jane 
4Laura 
5SamBoots
5SamScruff

 

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?

Need Help
  • hackcrr's avatar
    hackcrr
    2 years ago

    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.

     

4 Replies

  • hackcrr's avatar
    hackcrr
    Icon for Memorable Member rankMemorable Member
    2 years ago

    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.

     

     

  • crispybits77's avatar
    crispybits77
    Icon for Helper I rankHelper I
    2 years ago

    Thanks hackcrr 

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

    • hackcrr's avatar
      hackcrr
      Icon for Memorable Member rankMemorable Member
      2 years ago

      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.

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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