Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi guys!
I have come to understand that a universal date table is a pre requisite if I want to compare more than one date per row in a visualization (start/end, open/close, hire/termination and so on). My main source of inspiration this far is @amitchandaks article https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
Problem:
My main set of records where created during current or previous two calendar years, so I am used to a slicer looking like this:
However, when I create a relation between the two table my hierarchy break and I am left with individual dates.
If i try to use the hierarchy from the date table my slicer span from 2021 to 2121, and I can't quite figure out why.
Questions:
- Is there a way to create a relation between my main table and date table without breaking the hierarchy?
- Should I use the date table for my slicer and filter it by dates available in my main table?
Best regards,
Fredrik
@BooDaa , No. The join column will not have a hierarchy. Also, Date of date column used as date(when you mark as a date table) will not have a hierarchy
You can use date keys to join (so date retain hierarchy)
Datekey = year([Date])*10000 + month([Date]) *100 + day([date])
or duplicate the date
Date1 =[Date]
The new dates or some other dates can have a hierarchy.
I personally do not use default date hierarchy, it can break time intelligence
I create columns in date table and use those
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
99 | |
95 | |
88 | |
70 |
User | Count |
---|---|
165 | |
131 | |
129 | |
102 | |
98 |