Date column dissapears with relation

[Anonymous]

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 example I want to count the rows in my fact table for the year 2020, with this measure: 

Count2020= COUNTROWS(FILTER(DIM_FACT, [DATE].[Year] = 2020)), but the Year function does not work anymore. How can I fix this, and why does dit happen?

[bcdobbs · Accepted solution]

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]

@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