Slicer for two queries

[procyon82]

Hi,

A while ago I asked how I could remove outliers from my sales data to show 'truer' averages. The solution was to create a new query which works great.

How do I, however, use one date slicer now on a page that shows avg. revenue (query 1) and total revenue (query 2). Either query doesn't recognize the other's date columns as the tables are not linked. It tells me: you can't create a relationship between these two columns because one of the columns must have unique values.

[procyon82]

I think I know why. I have transaction ids which are supposed to be unique, but a few are just zero. 

How can I fix this? I'm importing the numbers from Google Analytics.

[CahabaData]

So if I understand your post; you are importing data that has a conflict flaw in the ID field of multiple 0s.

Call the imported table 1.  You would want to create a 'Table 1 Clean'. 

In the data view on the left frame, and in the Modeling tab in the ribbon you select the New Table option which will offer you then an expression field just below the ribbon.  Here is where you would define your new table:

Table 1 Clean = FILTER(Table1,Table1[ID]>0)

press enter and it gets created.  Everytime you refresh your data source this will fire. 

[procyon82]

Would that filter out transactions that are tied to the invalid ID?

[CahabaData]

in Table 1 Clean there would be no record where the ID = 0

in another table - let's say Table 2 - that record is still there - but of course it has nothing to join to in Table 1 Clean so it would depend on your method of looking at the data.  It is still seen unless your expression is to only show data that matches Table 1 Clean.