Data from 2 tables not interacting

[Anonymous]

Hi Everyone,

Been trying to figure out this issue for quite some time. I have 2 tables I'd like to interact with. The difference between the 2 tables is one is showing figures for last year and one from this year with some additional customers in the last year however, majority of customer names match

Here is the data from last year and this year:

Month is being taken from last year but there is also a Month column in this year's table

As you can see, the data works fine. However, when I add in the customers column to further drill into it, it only correctly displays the values of the table I add it from:

Ex. if I add values from last year:

(Hiding customer name due to sensitive data).

Here is my relationship between the 2 tables:

Relationship is on Customer

Any help or insight would be greatly appreciated.

Thanks!

[Ashish_Mathur]

Hi,

You should ideally append the 2 datasets.  Then create a Calendar Table and build a relationship (Many to One and Single) from the Date column of the appended dataset to the Date column of the Calendar Table.  In the Calendar Table, write calculated column formulas to extract Year, Month name and Month number.  Sort the Month name by the Month number.  To your visual, drag the Year and Month name from the Calendar Table.  Create a single column table of unique customers from the appended table.    Create a relationship (Many to One and Single) from the Customer column of the appended dataset to the single column Customer table.  Write this measure

Revenue = sum(data[Value]) 

[davehus · Accepted solution]

Hi @Anonymous, Your issue seems to lie with the month column. As it's more than likely being added from the table with the correct number showing in your example. As it's a dimension with no link to the other table, PBI doesn't understand the relationship context. You would need to merge the tables together as one or develop the correct star schema to prevent issues like this.