Forum Discussion

jdiruzza's avatar
jdiruzza
Frequent Visitor
2 years ago

Date Table Relationship wrong after adding a measure

I think I'm having trouble with my relationship between my date table and my staff detail.

 

When I add Days & Snapshot Date to a table everything looks great but if I add a measure it adds a blank into the date table and duplicates the snapshot date for every line of the Date Table.  The measure I have in the example below is a simple =1 just to show the issue.  

 

My date table is created from the StaffDetail:

VAR _mindate = min('StaffDetail'[SnapshotDTS])
VAR _maxdate = Max('StaffDetail'[SnapshotDTS])

RETURN
    CALENDAR (_mindate, _maxdate)
 
RELATIONSHIP:

 

BEFORE ADDING MEASURE:

 

After Adding Measure:

 

 

Need Help

3 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    2 years ago

    jdiruzza 

    When you create a dates table n Popwer BI it has to have the complete dates, partial dates will cause issues. Pleas modify your calendar table as follows and try:

    My date table is created from the StaffDetail:

VAR _mindate = date( year(min('StaffDetail'[SnapshotDTS]) , 1 , 1)
VAR _maxdate = date( year(max('StaffDetail'[SnapshotDTS]) , 12 , 31)

RETURN
    CALENDAR (_mindate, _maxdate)






  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi jdiruzza ,

    From the information given, I'm guessing the problem is in the StaffDetail table.
    Please check if [SnapshotDTS] contains null values. If it does, there is no value corresponding to it in Date [Date] and the engine will automatically create a null value to correspond to these values.
    Please check if these empty records make sense, and if not, consider filtering them out in powerquery.

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group