Sum Value in two different table based on a single date filter

[Anonymous]

Hi Guys,

So i am not sure the subject properly describes the issue, so i'd explain.

So i have two different tables (import from SQL)

Table One Contains All Transactions (2012 - Date)

Table Two Contains Transactions for a Specfic Service line(Eg, POS Terminals).(2020 - Date)

Both tables have datetime stamps with the customers ID linking both tables.

I would like to summarize both tables with visuals using just one date.(Eg Count of transactions on Table 1 + Count of trans on table 2).

So that one date filter connects both tables to filter values, counts, etc.

I have tried creating a calender table and then creating a relationship to it with table 1 and 2 but this doesn't work as I can view values from table 1( which contains more data) but table two truncates(Just shows one date)

Please i really need help on this as it a project i need to deliver on.

Thank you.

[v-lili6-msft]

hi @Anonymous 

If there is a relationship that between table1 and table2? If yes, you couldn't create two relationship between date table and table1/table2 at the same time, since it will lead to circular dependencies problem, for example:

If you have create a relationship between table1 and date table.

when you filter a date table, then it will filter table1, now table1 will filter table2 too. But you want to create another relationship between table2 and date table, now date table will filter table2 and table will filter table1, this is a circular dependency.

Regards,

Lin

[Greg_Deckler]

What you are describing doesn't make any sense. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

If you have:

Table1 *<--1 Calendar 1 -->*Table2

You use Calendar[Date] in your visual and a measure like: Measure = COUNTROWS('Table1') + COUNTROWS('Table2') I do not see why Tabl2 would be truncated to a single row.

[Anonymous]

Thanks for the feedback. So here is a summary.

1. I have created the date calender table

2. I have also linked the two tables to the calender tables using the date column.

3. However, one of the relationship is currently inactive and so the NewCalender filter only shows results from the active table relationship

See attached,

[Greg_Deckler]

Well, if it is inactive you likely have a second pathway between those two tables. Recreating the relationship will not work if this is the case. You have few options, you can use USERELATIONSHIP in your measure/column. You can try to edit your relationships so that you do not have duplicate paths between tables.

[sevenhills]

Try deleting the relation and recreate ... once the relationships are active, then it should be good.