Filtering table with missing data

[Anonymous]

I'm new to Power BI and I'm creating a dashboard with a few different sources. Most of the sources can be linked by a customer ID, but one cannot (it's just a summarized table of site visits). The dashboard includes several (about 😎 customer filters that I'd like to apply to the entire page, for instance Product, State, Brand, etc. For the tables that include customer ID, I have all the info I need to be able to filter, and the tables can be joined by customer ID. However, I only have the breakdown for a couple of these filters in the summary table. Additionally, all tables are linked by the field "Start Of Week". I created a number of index tables for these filters.

The problem is that it won't be clear to the user what data is linked, so I'm trying to get the summary visualizations not to show any data when the user filters on a field that doesn't exist in the data source. As in, when you filter for "state" most visualizations will filter correctly, but the whole summary visualizaiton appears blank, because we don't have that breakdown available for that source. 

Does anyone know an easy way to do this? I have tried adding columns for the missing filters, just putting the valuable as "N/A", but I've been getting the error: "You can't create a direct active relationship between [my summarized table] and [my index table] because that would introduce ambiguity between [index table] and [my primary table with full customer data]."

Thank you for any help!

[jthomson]

I don't see why it would create ambiguity - I've got data models where I've got separate data tables for job requests and job starts, which I can both have relating to tables listing customers asking for the job and suppliers we've used for it (and other stuff like date tables) without any problems (obviously in such a way that there's no duplicates), and my slicers work fine

[Anonymous]

Hm, not sure why I'm getting an error. To be more specific: 

I actually have several tables similar to table 2 that are standalone tables but have some similar fields (let's say Table 2, Table 3, Table 4 all have a Product field and a StartOfWeek field). 

I have index tables for State, Product, and StartOfWeek that I've successfully connected to Table 1. I was also able to connect these index tables to Table 2, even though in Table 2 State is just a field where are the values are blank. When I try to connect the index tables to Table 3, I am able to link StartOfWeek to Table 3, but when I try to link either of the other index tables, I get an error that says: "You can't create a direct active relationship between [Table 3] and [State index table] because that would introduce ambiguity between [State index table] and [Table 2]."

Does that context help explain the error? 

[v-huizhn-msft · Accepted solution]

Hi @Anonymous,

If you want to create a relationship betweem two tables, you only use one common column to create it. You related Table1 and Table2 using ColumnA, then you also want to create another relationship using ColumnB, you will get an error.

Best Regards,
Angelia

[v-huizhn-msft]

Hi @Anonymous,

After review and research, I am still confusing about your requirement, do you mind create a sample table and test list the expected result clearly, so that we can post detailed solution.

Best Regards,
Angelia

[Anonymous]

Hi @v-huizhn-msft, thanks for taking a look at this. Here are two sample tables to show what I mean (a simplified version of the data I'm dealing with) 

Table 1

Cutomer ID	Product	State	Transaction date	Promoter Score
X12302	A	MA	2-Dec	8
X50968	A	MA	3-Dec	2
X38495	B	RI	3-Dec	7
X37392	B	NY	4-Dec	9

Table 2

User ID	Product	WeekStart	Weekly Site Visits
A10	A	3-Dec	4
A11	B	3-Dec	1
A12	B	3-Dec	2
A13	B	3-Dec	4

I built an overview page in my report that includes visualizations from table 1 and table 2 (as well as others). In this example I want page slicers for Product and State. I used an index table to create a slicer for the Product field, which has been working for visualizations based both Table 1 and Table 2. For the State slicer, since the State field doesn't exist in Table 2, I want visualizations based on Table 2 to appear blank when the State slicer is used.

I tried creating a new State column in Table 2 where the values are blank or N/A, but when I tried to join this to Table 1 using an index table, I got an ambiguity error. 

Any help is appreciated! 

[jthomson]

Maybe you could work around things by adding a dummy State column in table 2 with nothing but null data and then trying to join that with the existing State column, using that in your slicer?

[Anonymous]

I tried this, and got this error: "You can't create a direct active relationship between [Table 2] and [my index table] because that would introduce ambiguity between [index table] and [Table 1]."  Any idea as to why that is? I don't understand the error - is there a way to override?