none
Tabular model dates in Excel

    Question

  • I've made a tabular model with a fact that's linked to a datedim. That's working fine.

    But my client dimension also has some dates (like Date of Birth). Now I want my users to have the option to select people with Date of Birth in some years and that kind of stuff. But it's not working. Excel recognizes the date of birth column not as a date:

    So you get the wrong filter options (for string instead of date)

    It is a datetime in model and I've also tried to connect a second date dim to it. Like this:

    But nothing works.

    Who can help me?


    Monday, July 2, 2018 7:29 AM

All replies

  • Hi MiKeHendriks,

    Thanks for your question.

    As we talked in your earlier post, only the dates in Date dimension will be used as a date, the date fields in other tables will be used as string.

    Thus in your scenario, you can reimport the date dimension, rename it as DateBirth, then the date fields in table DateBirth will be used as a date. Please note that the Date of Birth field in your client dimension will still be used as a string. See below blog talking about this:
    http://biinsight.com/role-playing-dimension-ssas-tabular/

    While this method that reimporting date dimension is not suitable if you have a big date table and have more then 3 date fields need to be used as date. In this case I would suggest you to create multiple relationships between Date dimension and Fact table, activating one relationship for each measure through the USERELATIONSHIP function in a CALCULATE statement. Please refer to below about DAX function USERELATIONSHIP:
    http://radhajyotsna0.blogspot.com/2015/08/role-playing-dimension-in-tabular.html


    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, July 3, 2018 1:24 AM
    Moderator
  • Hi willson yuan,

    Thank you for your reply. I understand what you mean. I thought/hoped that adding a second datedim to something like date of birth would give extra options to that field instead of a datedim. This can mean that I need 10 or more datedims in 1 model (or 1 with many relations in DAX), only for questions like "I want all the customers born between x and y" or "Give me all the customers products that have been available for sale from x to y". These are questions that can be really easily solved by Excels datefunctionality. But as I understand you correct this is not an option in the Tabular solutions?

    Tuesday, July 3, 2018 11:58 AM
  • Hi MiKeHendriks,

    Thanks for your response.

    >>>I thought/hoped that adding a second datedim to something like date of birth would give extra options to that field instead of a datedim. This can mean that I need 10 or more datedims in 1 model (or 1 with many relations in DAX), only for questions like "I want all the customers born between x and y" or "Give me all the customers products that have been available for sale from x to y".
    If you need 10 or more datedims in 1 model, then you might need to consider to redesign you data model, or handle that before importing to data model through ETL.

    >>>These are questions that can be really easily solved by Excels datefunctionality. But as I understand you correct this is not an option in the Tabular solutions?
    I guess this is something that you can achieve by writing DAX formula. See below blog:

    https://powerpivotpro.com/2012/02/lookups-based-on-date-ranges/


    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

    Thursday, July 5, 2018 6:36 AM
    Moderator

  • If you need 10 or more datedims in 1 model, then you might need to consider to redesign you data model, or handle that before importing to data model through ETL.


    Yes, you're right. 10 is really much. But we do need 4 or 5 dates sometimes. People want to analyse only sales of products that are introduced in a specific period for customers that are emailed in another specific period, but that didn't have an other order in another specific period.

    Such kind of things must be possible.


    I guess this is something that you can achieve by writing DAX formula. See below blog:

    https://powerpivotpro.com/2012/02/lookups-based-on-date-ranges/

    I don't see anything at all here that I can use.... I've posted an PBIX below; do you have any clue for me?

    URL of pbix: https://ufile.io/87rxl

    The strange thing is that date in a separate, second, date dim works fine. But when using something such as related to place it in the customer dim it becomes a varchar again....


    Friday, July 6, 2018 6:22 AM
  • Hi MiKeHendriks,

    Thanks for your response.

    I have download your sample PBI file, it sounds like that your original issue was resolved. Excel recognizes the date of birth column not as a date, according to below image, it was listed as date after mark the table as date:


    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, July 9, 2018 1:01 AM
    Moderator
  • No I don't think it is.... When I take the date from 'date_of_birth' dimension I get the 'valuefilters' and 'datefilters' options. But when I take the date of birth fields from the 'cust' dimension I get the 'valuefilters' and 'labelfilters'....

    Wednesday, July 11, 2018 7:03 AM
  • Hi MiKeHendriks,

    Thanks for your response.

    >>>When I take the date from 'date_of_birth' dimension I get the 'valuefilters' and 'datefilters' options. But when I take the date of birth fields from the 'cust' dimension I get the 'valuefilters' and 'labelfilters'

    Yes, this is what I have posted in my first reply for this thread.

    only the dates in Date dimension will be used as a date, the date fields in other tables will be used as string.


    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

    Thursday, July 12, 2018 7:21 AM
    Moderator