How to create a relationship between two mutually exclusive tables to show count as per month wise

[SantoshKumar]

Hi,

I have Two tables which have same structure but does not contains same data at all.
i am trying to show the count of items as per Month, quater and year wise.

i used scorecard with the use of Measures for calculating count .
when i select any month or year, data reflecting only from one table. i undertood that its the issue due to relationship between the two table but i have tried all relationship available but none of them gave the expected result.
Could anyone help me to solve this issue.

Thanks

Santosh Kumar P

[SantoshKumar · Accepted solution]

Hi,

I have got the answer for my Question.

Creating a Unique calculated column in both the tables and creating a relationship will provide the expected solution.

here is the link from Microsoft Power Bi Community tutorial which has the solution:  https://youtu.be/GarBXef0Vew 

Thanks

Santosh Kumar P

[SantoshKumar]

This is the Sample data for reference.

Table1
ID	Item	CreatedDate	Tags
1	18961567	9/12/18 12:14 AM	aa,bb,cc
2	18961169	9/11/18 11:46 PM	aa,bb,cc
3	18960482	9/11/18 11:01 PM	aa
4	18959438	9/11/18 9:54 PM	bb
5	18959335	9/11/18 9:48 PM	cc
Table2
ID	Item	CreatedDate	Tags
6	18958022	9/11/18 4:47 PM	bb,cc
7	18957726	9/11/18 6:08 AM	aa,cc
8	18954240	9/11/18 5:28 AM	bb
9	18953916	9/11/18 5:25 AM	cc
10	18953883	9/11/18 5:23 AM	aa

[amitchandak]

It is not possible to join both tables with the common date and Item dimension and take results out?

A calendar can be created in power BI.

[SantoshKumar]

@amitchandakdata is huge and joining them is not a good option i think.

below is the sample data posting for understanding. can you give me any reference of creating a calender , i will try to see whether it will work.

Table1
ID	Item	CreatedDate	Tags
1	18961567	9/12/18 12:14 AM	aa,bb,cc
2	18961169	9/11/18 11:46 PM	aa,bb,cc
3	18960482	9/11/18 11:01 PM	aa
4	18959438	9/11/18 9:54 PM	bb
5	18959335	9/11/18 9:48 PM	cc
Table2
ID	Item	CreatedDate	Tags
6	18958022	9/11/18 4:47 PM	bb,cc
7	18957726	9/11/18 6:08 AM	aa,cc
8	18954240	9/11/18 5:28 AM	bb
9	18953916	9/11/18 5:25 AM	cc
10	18953883	9/11/18 5:23 AM	aa

[amitchandak]

Dates = Calendar( Date(2015, 1, 1), Date(2020,12,31))

You can calculate Year, Month, etc as per need.

For item See if the union, List.Union can work for you

[SantoshKumar]

@amitchandaki have created calender table and tried to apply all kinds of relationships which are available but data reflection as per selection is not happening. Do i am missing some changes ??
any suggestions could be of great help ..