Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Date column dissapears with relation

Hi, My fact table has a date column. When I make a relation on it with my date table, the fact date column does not show as a date column anymore. I can't use the date functions for that column, for...
Need Help
  • bcdobbs's avatar
    bcdobbs
    4 years ago

    By default Power Bi creates hidden date tables in the background that you're referencing in your measure with [DATE].[Year]

    When you create a relationship to a full date table it turns that functionality off. 
    You need to update your measure to use your date table. Assuming your date table is called date and you've marked it as a date table use:

    Count2020= COUNTROWS(FILTER(DIM_FACT, 'Date'[Year] = 2020))

     

    Your DAX would be faster if you did this though:

    Count2020 =
CALCULATE (
    COUNTROWS(DIM_FACT),
    'Date'[Year] = 2020
)
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

Anonymous , when you use a date in join then it loses the date hierarchy, means you will not be able to use .date, .year, etc.

 

If needed, you can create a custom hierarchy

or you can have another column

date 1= [date ]

 

 

 

These are the reasons Date Hierarchy can be missing
https://community.powerbi.com/t5/Desktop/Date-Hierarchy-Doesn-t-show/td-p/525460
https://community.powerbi.com/t5/Desktop/Date-hierarchy-not-available/td-p/438804
https://community.powerbi.com/t5/Desktop/Lost-Missing-Date-Hierarchy/td-p/421045

Check Settings
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-auto-date-time