Why not a date and time table in Power BI instead?

Рет қаралды 16,784

Күн бұрын

Пікірлер: 19

@nikkibeak4606 2 ай бұрын

I like adding 2 additional columns is_month returns current month, previous month or month name and is_today returns today, yesterday or date. This allows me to set the power bi slicer to current month without the need to update the slicers each month

@GuyInACube 2 ай бұрын

Yup. That is a great solution.

@joxuli89 Ай бұрын

Great video!!! Self-learnt Power BI back in 2018-19, then it took me 2+ years to persuade my company's data team to take my DATE table into the company's data lake and then the data warehouse. Now 2024 is ending, still find I am probably the only proactive user of the DATE table in my company's data warehouse 😂

@ezequielalmeida6378 2 ай бұрын

I'm still fighting with my boss about upgrading power bi PRO to Fabric licenses, can't wait to try all this out!

@thghtfl 2 ай бұрын

it seems you haven't been able to prove to your boss that it's worthwhile. is it though?

@geehaf Ай бұрын

Great video. When using Notebooks/Spark SQL in Fabric I've found this useful to use as a CTE to base the generation of your date table from: WITH date_range AS ( SELECT explode(sequence( to_date('2020-07-01', 'yyyy-MM-dd'), add_months(to_date('2020-07-01', 'yyyy-MM-dd'), 12*10), interval 1 day)) AS the_date )

@sbdyelse 2 ай бұрын

Any organisation should have a chief date officer !

@DPinderGMail 2 ай бұрын

I always have a date table in my SQL data warehouse, which I can then ingest into whatever platform I am using for reporting. I am going to be setting up a Fabric DWH in the near future, which will also make use of this table, along with other common tables (such as lookups, staff info etc).

@melodym5993 2 ай бұрын

You're brilliant!!!! Always learn something new, and get great ideas from you sharing your knowledge. Thanks so much!!

@GuyInACube 2 ай бұрын

Awesome to hear! Makes the work worth it.

@palashgangal951 10 күн бұрын

Q: I am trying to create a power BI 1 PAGER index that holds hyperlinks to all my power BI reports published on service. I created 4 columns Sr. No. / Report Name / Links / last refresh date. I was able to populate everything else by creating a manual table but not able to get last refresh date of each individual report. Can this video help me?

@Sevententh 2 ай бұрын

Is GENERATE_SERIES now available in Fabric SQL then??, as about 9 months it wasn't...

@chrisshort5711 2 ай бұрын

First, thank you for your videos, they've been so helpful to me as I get started in Fabric! Very timely video... I was just working on this same solution over the past few days. I completely agree with your reasoning for why this should be done in a table instead of just in PowerBI. I would love to hear you follow up on a few complaints/questions I have after going through this process: 1.) I started by writing SQL like you did but you can't generate a table in a Lakehouse via SQL, so I moved to notebook code instead. I like the idea of a shortcut from Warehouse back to Lakehouse as a workaround, but this seems to violate the Medallion architecture pattern. thoughts? Seems like MS should really add the ability to create tables via SQL in lakehouse to better support this type of scenario. Not sure why it can be supported by notebooks but not the sql interface. 2.) I'm fairly new to PowerBI (much more experience with Tableau) and the way PowerBI handles dates just seems absurd to me. Tableau would automatically generate all date hierarchies even in published data sources (equivalent to connecting to a semantic model). The fact that I have to manually create hierarchies for any date I want to use is a real pain. Thoughts on why MS went this route? What benefit does it have over Tableau's implementation? 3.) One thing I didn't realize at first when setting up these hierarchies is that the bottom of the hierarchy needs to be a Date type field in order to get the sorting to work by default. I initially tried Year#-Month#-Day# and this resulted in defaulting to ordering by numeric values descending.