Forum Discussion
How to create a relationship between two mutually exclusive tables to show count as per month wise
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
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
6 Replies
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.
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 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
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 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
