cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Filtering table with missing data

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-huizhn-msft
Microsoft

Re: Filtering table with missing data

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

View solution in original post

7 REPLIES 7
Highlighted
Microsoft v-huizhn-msft
Microsoft

Re: Filtering table with missing data

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
Not applicable

Re: Filtering table with missing data

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 IDProductStateTransaction datePromoter Score
X12302AMA2-Dec8
X50968AMA3-Dec2
X38495BRI3-Dec7
X37392BNY4-Dec9

 

Table 2

User IDProductWeekStartWeekly Site Visits
A10A3-Dec4
A11B3-Dec1
A12B3-Dec2
A13B3-Dec4

 

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 New Contributor
New Contributor

Re: Filtering table with missing data

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
Not applicable

Re: Filtering table with missing data

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? 

jthomson New Contributor
New Contributor

Re: Filtering table with missing data

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
Not applicable

Re: Filtering table with missing data

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? 

Microsoft v-huizhn-msft
Microsoft

Re: Filtering table with missing data

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

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,921)