cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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

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.

5 REPLIES 5
Highlighted
Super User IV
Super User IV

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

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.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper I
Helper I

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

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,

20200516_000117.jpg

158958385803291491804567318381.jpg

 

Highlighted
Helper II
Helper II

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

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

Highlighted
Super User IV
Super User IV

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

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.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Community Support
Community Support

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

hi @KingsleyIshgz 

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

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

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Kudoed Authors