Forum Discussion
Relationship between Two Tables using Date Column
Hello, I tried to Create a Relationship using Date Column from A Table to B Table and but I am getting below result, where B Table Count is repeated for each Month.
Note: Month Column is from Table A which will also be used for Filter as well.
Please do help me.
6 Replies
Hi, check if the relationship is active, what is the column in Table B that you are using for linking? Can you take a snapshot from Manage Relationship Window?
olgad Thanks for your response. The issue occurs when you put the data in same table.
Date and Count from Table A + Count from Table B -> In Same Table. as show in screenshot above.
In below screen shot - Main Table is Table A and Report is Table B. I created a relationship between startedDate - Created Date. In Cross Filter direction, I have tried all 3 options and still same results.
It is a general practice that you create a separate date table for your report.
Many to Many relationship often leads to confusing results. From Date table you can create ONE to MANY reölationships to both tables and use the Date columns in all your visuals and calculations.
The easiest wy to create a date table is to use this expressionDate = CALENDAR (DATE(2013,1,1), DATE(2023,12,31)) orDate = CALENDAR (DATE(2013,1,1), DATE(Year(Today()),12,31))depending on your data.olgad As per your suggestion I tried creating the Date Table seperately and Link between two Data Tables.
1. Created Date Column as per your instructions
2. Created One To Many Relation Ship (But Automatically convert as Many to One)
3. Choosen Cross Filter Direction: Single (Even I tried Both)
Result: Now Its not showing any Month Names.
Create on the Date table the column Month
Month = FORMAT([Date], "MMM")&","&'Date'[Year]and put that column into the visual
