none
Datefields in tabular model lack datefunctionality in Excel

    Question

  • Hi all,

    When I mark my date dimension as a datetable I get all the wanted functionality for this date in my Excel. I get things like 'last year', 'previous quarter' and so on. That's what I want.

    But that only works for the date dimension. Other dates that I have; like describing dates in Dimensions (such as Date of Birth and so on) are marked as date in the properties of the field in my model, but Excel doesn't give me the 'datefilter options' but the 'stringfilter options'.

    Somebody knows thus problem? And how to solve this?

    Friday, June 15, 2018 7:25 AM

All replies

  • Hi Mike,

    There are two things in this if I understand you correctly. You have a date dimension and you have marked the date dimension table as date and excel recognises it as a date table and you get all the required functionality.

    Where as there is another column called date of birth column which is present in another dimension which has date format but it does not provide you the same functionality.

    The reason which I can see is that for date table the lowest leaf level (date) is always unique where as i you case Date if birth is no the key column it would be repeated. Also when you try to mark this table as a 'Date' table in SSAS the tabular model will not allow you to mark this table as a date table.

    Have you tried marking this as a 'date' table?

    HTH,

    Ram


    Please vote as helpful or mark as answer, if it helps

    Friday, June 15, 2018 9:10 AM
  • Hi Ram,

    You're right. That's exactly my problem. But I can't/won't mark my employee table as a date table and to be honoust; if I was able to I still was in trouble because of that there are more than 1 dimension with dates in it.

    So I wan't to be able to show me everyone who is born in year X or in month Y. And I can't do this at all....

    That's really a problem for us; in SSAS Multidimensional everything worked without the need to modify any settings at all. Just import a SQL Server datetime field and everything went good...

    Friday, June 15, 2018 11:23 AM
  • Hi Mike,

    I believe the same can  be achieved by treating your dimension table as a fact and link the DOB to your date dimensions and create the required report?

    HTH,

    Ram


    Please vote as helpful or mark as answer, if it helps

    Friday, June 15, 2018 3:13 PM
  • Hi,

    I don't understand? How can i link all my dimensions with 1 datedim?

    And what is DOB?

    Friday, June 15, 2018 5:27 PM
  • Ok, what I meant is you can add your dimensions as a fact and link the date column of your fact with the date dimensions.

    In your example, Date of birth field (DOB) is coming from a different dimension. So what I am suggesting is to add this dimension as a fact and link the date of birth field with the date dimension.

    In this way you can use the same date dimension filter to create the reports.

    HTH,

    Ram


    Please vote as helpful or mark as answer, if it helps

    Sunday, June 17, 2018 12:51 PM
  • Hi MiKeHendriks,

    Thanks for your question.

    >>>Somebody knows thus problem? And how to solve this?
    If I understand your correctly, you want to implemnet role playing diemension in SSAS Tabular model, right? However role playing dimension does not support in SSAS Tabular model. In this scenario, you can try to reimport your date dimention table again, and rename it as DateOfBirth table, then you can create relationships between these two tables, in the end, you can get things like 'last year', 'previous quarter' and so on. 

    please refer to below blog:
    http://biinsight.com/role-playing-dimension-ssas-tabular/

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, June 18, 2018 1:40 AM
    Moderator
  • Hi MiKeHendriks,

    Thanks for your question.

    >>>Somebody knows thus problem? And how to solve this?
    If I understand your correctly, you want to implemnet role playing diemension in SSAS Tabular model, right? However role playing dimension does not support in SSAS Tabular model. In this scenario, you can try to reimport your date dimention table again, and rename it as DateOfBirth table, then you can create relationships between these two tables, in the end, you can get things like 'last year', 'previous quarter' and so on. 

    please refer to below blog:
    http://biinsight.com/role-playing-dimension-ssas-tabular/

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    &

    Ok, what I meant is you can add your dimensions as a fact and link the date column of your fact with the date dimensions.

    In your example, Date of birth field (DOB) is coming from a different dimension. So what I am suggesting is to add this dimension as a fact and link the date of birth field with the date dimension.

    In this way you can use the same date dimension filter to create the reports.

    HTH,

    Ram


    Please vote as helpful or mark as answer, if it helps

    No, I want ALL the datecolumns to be available as a datefield, with all the default Excel options. I don't need it to be connected to a datedim, but just be labeled as a date to get these options:

    Monday, June 18, 2018 5:29 PM
  • Hi MiKeHendriks,

    Thanks for your response.

    >>>No, I want ALL the datecolumns to be available as a datefield, with all the default Excel options. I don't need it to be connected to a datedim, but just be labeled as a date to get these options:
    As Ram and I said in our previous reply, to get these options, you could try to reimport your date dimention table again, rename it as DateOfBirth table, then you can create relationships between these two tables, in the end, you can use fields in DateOfBirth table to get things like 'last year', 'previous quarter' and so on. Other wise the date column will be treated as label filters or Value filters.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, June 19, 2018 8:43 AM
    Moderator
  • I've tried it in Power BI (Tabular as well imo) and it doesn't work. I've added a second datedimension and marked it as a datetable and connected it to date of birth. But nothing changed in Excel.

    And apart from that; if I have 21 datefields in my dimensions I really need to add 21 extra datedims? That's the 'normal' way?

    Pics:

    Tuesday, June 19, 2018 5:37 PM
  • Hi Mike,

    In Power BI, if you mark Date as a "date column", you will be able to get additional drop down like relative, before, after, between and default drop down option.

    Can you please provide screenshots between you actual "date dimension column" and "date of birth column" and let us know what you are missing both from excel and power BI side?

    Thanks and regards,

    Ram


    Please vote as helpful or mark as answer, if it helps

    Wednesday, June 20, 2018 1:44 PM
  • Hi Ram,

    Connection between DOB and second datedim is this:

    It's marked as datetable:

    And when I  analyze in Excel the 'geboortedatum' column I get this:

    Does this help you to see what's going on?

    Thursday, June 21, 2018 6:33 AM
  • Little kick.
    1 hour 3 minutes ago
  • testttttes
    1 hour 2 minutes ago