I finished watching the Power BI Super Users Workshop.
I noticed that a Date table was created based on a query in which we have to insert the start date and the end date for our table.
The question I want to ask is:
Why not create a Calendar table in DAX that will read the dates from the oldest and newest records and thus dynamically create a Calendar table if we have to worry about whether the table created in Power Query still meets the date requirements of our fact table?
You could use DAX as well to create a Date Table. As a best practice we use Power Query, but you are right in thinking that you could use DAX as well. I would highly recommend using the Power Query version, but the choice is up to you. Here are a couple resources if you are interested in developing a DAX Date Table.
** Attached, is a file from Avi Singh, that he uses to create with DAX**
Sorry, I only saw the first video, which I loved, but I already got the answer I wanted.
Is it better to use Calendar or CalendarAuto functions in DAX, or build the date dimension using Power Query?
This is always the question that I get; where should I build my date dimension? My answer is always one thing: Wherever you feel more comfortable with it! You have to maintain this dimension table going forward, so you have to decide where you are going to build it. there are pros and cons of building it in everywhere, for example;
Calendar() and CalendarAuto() functions are very simple to use, and fast way of creating a date table.
I have so much to learn that it becomes scary.
As the questions arise and if I have not found the answer in the many workshops that you have available, I will come to you for your precious help.
Thank you so much for the availability.
The depth, flexibility and power that makes Power BI so fascinating is also what can make it seem incredibly daunting at the start. It’s impossible to master all aspects of it, and even if you could, with the monthly program updates, there is always new stuff to learn.
Here are a few things that I think will help guide you on the journey:
eDNA Learning Map - @sam.mckay has put together a fantastic organized learning plan for how to best utilize the wealth of content on the site.
eDNA Forum - this forum is an absolutely amazing community filled with incredibly talented, friendly people who are eager to help each other. Whenever you get stuck or just have a question you want input on, post it on the forum and you’ll probably get multiple answers/solutions. Here’s a guide to making the most of the forum:
eDNA Data Challenges - this is a new aspect of Enterprise DNA - bi-weekly challenges where you are given a real-world scenario and the associated data, and challenged to create a report that best analyzes and visualizes the data
It’s a phenomenal way to build your skills in the four pillars of Power BI (data modeling, Power Query, DAX and visualization), and learn from your peers. Everyone is welcome and encouraged to participate, regardless of experience level.
Strategies for Improving Your DAX and M Skills - this is a video I’m currently putting together for the Enterprise DNA TV YouTube channel that should post next weekend or so that I think provides some additional helpful tools and strategies for structuring your development that very much complements the Learning Map above.
I hope this is helpful. Good luck and enjoy the journey!