Issue with active vs inactive relationships between three tables

[ewong18]

Intro: I have three tables as shown below. i'm trying to build a gannt chart using dates from "table 1" with tasks tied to "ID." The dashboard page is built from table 2, all except the gannt chart because the dates are not located in that table. I want to be able to click on visualisations and filter the gannt chart as well. I'm assuming this means I need to link table 2 to table 1. Additionally, there is a slicer on the page based on "field 1" in table 3.

Issue: When I try to build an active relationship between the "ID" in table 1 and table 2, it gives me an error because it would introduce ambiguity between table 2 and table 3. However, I do not want to inactivate the relationship in "field 1" between table 2 and 3 as that would break the slicer on this an other pages in the dashboard.

How can get this to work without breaking other visualisations? Thanks.

[v-lid-msft]

Hi @ewong18 ,

 If your Table 3 has relation between Table 1 and Table 2 based on the Field, There is no need to create relation between Table1 and Tbale 2, they will filter each across Table 3.

Left chart is from table 1, right chart is from table 2, when I click on the right visual, table 1 can be filted.

If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ewong18]

Thanks for the response. The visual currently doesn't filter on field2 and field3, I wonder if it's becuase field1 isn't a key of some kind. Unfortunately I cannot share the dataset as they do contain confidential information. What I can say is that field2 contains subcategories of field1 and field 2 contains subcategories of field3. On the page, there is a slicer based on field1, and there is a visualisation based on field2 and one based on field3. There's a third visual that is successfully filtered by both field1 and field2. All these three visuals are based on data from Table 2. I have added a fourth visual that requires the dates from table 1. Slicing by field1 works, but filtering on field2 and field3 do not.

Does that help clarify? Thank you

[v-lid-msft]

Hi @ewong18 ,

Sorry for that, we still cannot understand clearly, can you reproduce this issue in the sample pbix file we have shared?

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.