Forum Discussion

ctedesco3307's avatar
ctedesco3307
Icon for Resolver II rankResolver II
4 years ago
Solved

Loose my column dates when adding values or rows in matrix

Hello.  Can anyone tell me why when I add in my rows field I loose most of my weeks in my columns?   I’m trying to show a matrix of sum oof billable hours (values) across weeks(columns) by project (...
Need Help
  • ctedesco3307's avatar
    ctedesco3307
    4 years ago

    This is the solution to this topic posted by TheoC 

     

     

    Okay, so I figured out what the cause is.  The Date field in your Calendar table is fine. But the Date field in your Fact table is actually a DateTime field.  You need to create a Date field on its own and then create a relationship between the two tables with the Date fields only  

  • TheoC's avatar
    TheoC
    4 years ago

    Hi ctedesco3307 

     

    Glad I could help with the "Okay, so I figured out what the cause is.  The Date field in your Calendar table is fine. But the Date field in your Fact table is actually a DateTime field.  You need to create a Date field on its own and then create a relationship between the two tables with the Date fields only "

     

    In all honesty, I don't know the exact reason that the Date and Date/Time didn't work initially.  It could have been for a variety of reasons but within this example, I am unsure.

     

    All the best with your Power BI journey my friend.

     

    Theo 🙂

TheoC's avatar
TheoC
Icon for Community Champion rankCommunity Champion
4 years ago

Hi ctedesco3307 

 

A couple of things just to double check:

  1. The relationship from the Date to Fact table is one to many (one from Date, many to Fact table).
  2. Ensure that your Date table has all of the dates required in it.
  3. On your visual, make sure the Week Numbers is from your Date table.

Hope this helps 🙂

 

  • ctedesco3307's avatar
    ctedesco3307
    Icon for Resolver II rankResolver II
    4 years ago

    Hi TheoC - Thank you for the quick response, but all the things you mentioned are in tact. 

    1. The relationship from the Date to Fact table is one to many (one from Date, many to Fact table). - it is -- please see image in post 
    2. Ensure that your Date table has all of the dates required in it. - it does. It ha every day in the calendar year 
    3. On your visual, make sure the Week Numbers is from your Date table. - It is - 

    4.  

    Thank you for trying to help! 

    • TheoC's avatar
      TheoC
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Hi ctedesco3307 

       

      Can you send some sample data?

       

      Thanks heaps,

      Theo

       

      • ctedesco3307's avatar
        ctedesco3307
        Icon for Resolver II rankResolver II
        4 years ago

        Hi TheoC  Thank  you for trying once again, but unfortunaley that is not it. The  join between the two tables is between WOrKLOG_WORKLOG_Date and Date and they are both set as Date/Time. If one of those is showing as date for the column type it must have happened in the sample file creation.  I did try changing them both to date to see if it made a difference and it did not.  Thanks anyway