Table Visualization / Filtering / Relationships

[kotarosai]

Hello, I am having an issue with table filtering. I have two tables that are related to both an account and date table, an example of the relationship is below. Now in the report, I have the two tables side by side but when clicking on a row, it only filters the opposite table by date and cannot get it to filter by account. Is this possible? Please note the account slicer works for both tables as well, but would like it to work when clicking into the table. I appreciate your support in advance, thanks!

ONLY DATES:

FILTERING ACCOUNT:

[v-zhenbw-msft · Accepted solution]

Hi @kotarosai ,

Please allow us to confirm whether our understanding is correct.

Do you want to click on the field in the left table, and then want to filter on the right table?

For example, you click the McAllen in Current Month BIS, then filter the McAllen in Current Month Sales.

If your left table visual using the data in Benefits table and your right table visual using the data in Sales table, the two tables visuals will not filter each other, because the relationship arrows are not in the same direction.

Maybe you can use the Account name in Account table, the relationship is only passed in the direction of the arrow, please refer the following sample.

The following screenshot, the right table cannot be filtered, because the two table visual use their own data.

The following screenshot, we use the name in Table, then the right table will be filtered.

If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data or describe the fields of each tables and the relations between tables simply?

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.

[blopez11]

Hard to tell since I can't see what columns you are using in your table visuals, but I believe in your table visuals, you need to use the date column from your date table and id column or if you have an associate name column from your account table

[kotarosai]

Thanks @blopez11, you can see the relationship and fields below. I was using a calculated column to get the name of the account, but even using the ID which is what both tables are referencing, it doesn't seem to work and is filtering only on date:

[Greg_Deckler]

@kotarosai Would need to see the relationships in model view. Sample data would be great to mock it up.

[kotarosai]

Sure thing @Greg_Deckler , I don't have sample data but the relationship modeling looks like the below:

[Greg_Deckler]

@kotarosai Those relationships look legit. Unless, you have timestamps in one of those columns but your date table only has dates and then nothing would match and maybe it would act this way?

[kotarosai]

Thansk @Greg_Deckler, nope. No timestamps for either of the columns being referenced by the date table, which I guess which is why the date filter is the only thing working at this moment.

[Greg_Deckler]

@kotarosai What about doing a Clean and Trim on your account id columns?

[kotarosai]

Hey Greg, thanks for that idea but I don't think that's the issue as it works when putting it in the slicer, just not when clicking in the table. Please find below:

[v-zhenbw-msft · Accepted solution]

Hi @kotarosai ,

Please allow us to confirm whether our understanding is correct.

Do you want to click on the field in the left table, and then want to filter on the right table?

For example, you click the McAllen in Current Month BIS, then filter the McAllen in Current Month Sales.

If your left table visual using the data in Benefits table and your right table visual using the data in Sales table, the two tables visuals will not filter each other, because the relationship arrows are not in the same direction.

Maybe you can use the Account name in Account table, the relationship is only passed in the direction of the arrow, please refer the following sample.

The following screenshot, the right table cannot be filtered, because the two table visual use their own data.

The following screenshot, we use the name in Table, then the right table will be filtered.

If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data or describe the fields of each tables and the relations between tables simply?

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.

[kotarosai]

Thanks for your feedback @v-zhenbw-msft,

Yes correct, I want to be able to select the account on an account name on the right table to filter the left table by that account name. I added a date table in your sample file to make it more similar to my configuration, and while the filtering from Table 2 to Table 3 still works, it seems the filter from Table 3 to Table 2 does not work anymore. Does the order of creating / activating the relationships do anything to the behavior? Please find attached pbix and I appreciate your support!

https://drive.google.com/file/d/1f2iA4H75UjrT3ilelaLZH7vcOKVsTsXX/view?usp=sharing 

[v-zhenbw-msft · Accepted solution]

Hi @kotarosai ,

If you want to filter table 2 with table 3, there are two ways.

1. Table visual on the right also uses the name field in Table.

2. Or we can change the relationship between Table and Table 3 to both.

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.

[kotarosai]

Thanks @v-zhenbw-msft , while it still is not working in my main pbix file, your solution has worked in the sample pbix file I provided so I have marked it as best answer. Thanks for your support!

[amitchandak]

@kotarosai , Account is a common table it should filter both tables. Now if you do not want both tables(Visual) to interact. You can stop interaction Or Take a date from those tables. Do not take a common date.

[kotarosai]

Thanks @amitchandak, any reason why it would not be filtering both tables?

[amitchandak]

@kotarosai , Not very clear. If you looking to filter account and date by each other. In this case table a measure from each table and filter them as NON BLANK

refer this example : https://www.youtube.com/watch?v=cyOquvfhzNM

[kotarosai]

I appreciate your feedback @amitchandak, I am trying to filter both table visualizations by account instead of date by clicking into the table visualization. The video I watched unfortunuately does not help my case.