Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

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 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?
1 ACCEPTED SOLUTION
bcdobbs
Community Champion
Community Champion

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
)


Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

View solution in original post

2 REPLIES 2
bcdobbs
Community Champion
Community Champion

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
)


Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!
amitchandak
Super User
Super User

@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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.