Trouble Linking Tables for Matrix

[Leif_Eriks0n]

I am trying to link two separate tables for one matrix.

Table 1 has the following columns: Order Date, Time Interval (15 minutes), Venue Name, Service Type, Payment Method, Sales Amount

Table 2 has the following columns: Order Date, Time Interval (15 minutes), Venue Name, Service Type, Payment Method, Order Count

So, almost identical tables but one has sales amount and one has order count.  In my visual I want to show one matrix that has Venue Name for rows and Sales Amount and Order Count for values.  I also want to run a measure for average check, and I need this to be filterable by date.  My problem is, no matter how I connnect the tables (order date, venue name, etc.), I can't get both the Sales Amount and Order Count to filter at the same time.  Below is the data with no date filter:

Right now the tables are connected by Venue Name date slider is coming from the Table 2.  The numbers and average check calculation in the picture are correct.  However, when I move the slider to just one day (or any other range) the Order Counts change but the sales stay the same:

So now the transactions are correct but sales are not, and average check is thrown way off.  The same thing happens inversely if I use the order date from Table 1 in the slider - sales amount changes but the transactions do not change.

Does anyone know how to fix this?

[Leif_Eriks0n]

Thanks, this worked.  I had to create a new/duplicate table for everything I wanted to filter on, including Payment Method, Service Type, and Time Interval.

[Anonymous · Accepted solution]

Hi @Leif_Eriks0n ,

I suggest you to create dim tables like DimVenue and DimDate, then create relationships between them and Table1/Table2.

Result in my sample is as below.

By Default：

In January:

In February:

Best Regards,
Rico Zhou

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