Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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:
Solved! Go to 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
)
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
)
@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
User | Count |
---|---|
121 | |
69 | |
67 | |
57 | |
50 |
User | Count |
---|---|
176 | |
83 | |
69 | |
65 | |
54 |