I just got back from attending and speaking at the Power BI World Tour in Charlotte. What a great two days! Small sessions, great presenters, excellent content. You can’t beat that! I will be featuring a couple of ‘nuggets’ I got from the sessions I attended over the next couple of weeks.
My first ‘nugget’ comes from a session with Dan Edwards. His session was titled Time Intelligence, and I am so glad I went. I knew a good amount of what he covered, but that’s not important. My real gain was from what I didn’t realize.
1) I knew that it was best practice to have a date table. That’s one of the first things I explain to users in my training sessions. You can’t rely on the native dates built into your model to give you the extent of the modeling capabilities you need. But, what I didn’t realize is that you can’t use Power BI’s great time intelligence functions (such as YTD, QTD and so on) without a date table.
2) I typically create my date tables in the Power BI Desktop using DAX, but was always puzzled about how to edit that new table in the Power Query Editor. (Tables created in the Desktop do not show up in the PQE.) Solution? Simple. Create the date table in the PQE. Duh.
3) When you have multiple dates in your model, connect your date table to one of the date fields in your model’s main fact table. If you have multiple dates in your model, establish secondary (inactive) relationships between your date table and these other date fields. Then deploy the USERELATIONSHIP function in DAX to leverage these inactive relationship(s).
4) To use the Related capabilities in the Filters and Slicers, you need to use the Date field in your Date table. Meaning–don’t use a Month or Quarter field, or any field other than your Date field.
5) De-activate the native Date Hierarchy feature provided by default and create your own.
Date hierarchies deserve their own post, so I will save that discussion for another time.
Hope these tips help you as much as they helped me. And thanks to Dan Edwards and all the other presenters at Power BI World Tour 2018, who took time to prepare a presentation, travelled to the event, and shared their knowledge!